Quick Help with Reshape Command

[Ali Sheikhpour]

Hi just quick clarification needed on Reshape.

I start with data that looks like:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long regionname int(v8 v9 v10 v11 v12 v13 v14 v15 v16 v17)
91342 476 463 400 346 327 350 338 322 341 344
91335 357 318 297 255 242 233 231 230 237 265
91331 340 300 268 265 245 231 230 228 229 242
91801  54  54  55  57  61  64  59  66  70  74
91367 290 258 245 241 232 243 250 249 262 250
91406 288 247 230 200 188 190 186 182 185 189
91706 126 116 111 105 102 101 100 110 117 123
91766 116  94 117 124 128 138 129 131 152 160
91744 110 106 102 105 110 116 129 134 147 157
91702 110  96 110 104  94  99 102 112 127 142
91770  49  48  51  57  54  60  62  69  71  75
91304 276 250 221 204 193 187 184 186 200 220
91344 268 230 191 186 178 199 202 202 208 211
91745 104  98 134 148 145 136 140 147 152 167
91765 159 148 158 170 172 194 208 222 224 219
91605 142 130 130 117 101 107 116 120 124 126
91016  84  94 107 118 110 122 113 120 132 128
91320 250 226 229 232 216 203 214 213 204 214
91423 272 254 232 208 203 203 204 208 200 218
91732  44  42  48  45  47  47  56  63  75  77
91401 153 154 156 160 163 177 168 163 169 177
91360 168 156 158 156 129 137 140 148 157 152
91604 262 238 244 240 249 258 236 236 233 249
91767 121 119 113 112  93  98 114 117 143 149
91387 368 360 315 276 251 247 248 264 270 278
91748  74  64  73  82  82  85  85  94 107 117
91306 264 244 227 214 192 200 200 195 204 212
91607 118 112 109 102  82  87  72  83  89  96
91311 298 269 251 237 212 220 229 230 250 257
91362 336 302 283 262 236 254 270 282 247 259
91750 105 104 101  92  93 109 107 103 109 116
91107  90  96  98  97  98 110 122 127 126 138
91104  86  85  86  96  94  96  98  90  96  98
91789  86  66  84  91  90 102 114 131 135 144
91773  94  91 107 112 112 119 114 113 123 134
91711 111  96 106  99  98 108 106 120 135 144
91754  34  30  38  38  40  42  44  45  54  52
91355 256 278 249 237 213 215 210 214 216 233
91001 104  98 108 120 117 119 129 128 134 137
91350 242 262 247 234 224 220 218 223 250 265
91316 284 250 259 227 212 206 203 220 214 222
91780  56  56  64  62  60  64  69  74  78  74
91776  38  34  44  37  32  39  37  36  34  42
91325 152 154 117 120 115 109 126 128 140 146
91403 182 169 150 152 155 148 146 152 178 168
91352 138 147 138 114 108 116 106 108 115 115
91356 297 300 283 262 244 232 228 240 255 261
91007  57  62  61  72  76  85  85  88  85  72
91106  86  96  97 106 102 114 114 112 106 108
91722  60  58  67  72  82  96  94  98 108 116
91321 225 222 205 216 218 206 202 209 215 202
91791  74  82  85  86  88  88  91  95 102 110
91354 298 296 232 216 210 230 238 248 254 234
91006  82  86 101 104 104 115 112 118 110 112
91214  98 102  98  97  98 108 114 103 104  96
91030  36  43  49  52  46  41  45  48  47  45
91803  21  22  31  34  32  36  36  42  42  42
91351 315 308 274 242 226 215 218 234 238 238
91364 268 272 218 192 178 194 200 215 218 216
91042  93  99  98 100  94 102 102 101 102 110
91602 158 151 161 152 143 142 134 136 134 132
91752 109  96  90 103  93  94 106 118 120 119
91303  88  78  68  72  74  76  72  68  71  80
91504  94 102  93  94  93  94 106 104 102 106
91740  60  59  70  68  64  72  66  68  68  62
91302 431 406 373 342 332 316 309 324 317 312
91301 247 222 216 203 192 199 200 202 189 183
91792  52  46  65  66  60  70  78  76  86  92
91741  92 102 116 116 114 124 120 122 124 128
91411  97  87  80  68  66  74  76  74  74  70
91103  89  86  89  91  82  79  89 100 101  97
91724  58  54  62  64  68  74  82  92  92  98
91361 306 248 236 246 223 218 218 220 192 182
91307 220 200 173 166 153 167 178 174 179 180
91775  41  39  40  48  53  58  63  61  57  54
91768  82  71  76  74  70  68  69  74  78  86
91010  53  55  57  58  50  53  58  64  79  82
91040 123 129 119 114 110 114 115 120 117 104
91384 217 196 180 170 154 164 172 179 177 182
91011  88  76  77  84  75  81  80  75  86  81
91381 188 184 151 152 135 161 169 166 165 159
91746  40  38  47  51  50  45  46  50  50  56
91390 226 220 186 174 168 158 160 169 177 168
91105  78  70  64  72  74  86  88  84  81  84
91436 161 144 110 110 104 102 108 120 119 124
91024  36  36  37  50  52  52  52  51  52  54
end

I run:

rename (v8-v106) X=
rename X* X#, renumber

gen long obs_no = _n
reshape long X, i(obs_no) j(month)

And end with data that looks like:

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long regionname byte date int X
91001  1 104
91001  2  98
91001  3 108
91001  4 120
91001  5 117
91001  6 119
91001  7 129
91001  8 128
91001  9 134
91001 10 137
91001 11 134
91001 12 121
91001 13 112
91001 14 116
91001 15 120
91001 16 123
91001 17 131
91001 18 133
91001 19 136
91001 20 144
91001 21 116
91001 22 114
91001 23 119
91001 24 114
91001 25 106
91001 26 111
91001 27 118
91001 28 111
91001 29 103
91001 30  98
91001 31  96
91001 32  91
91001 33  94
91001 34  95
91001 35 100
91001 36  83
91001 37  76
91001 38  80
91001 39  72
91001 40  58
91001 41  76
91001 42  68
91001 43  82
91001 44  78
91001 45  68
91001 46  64
91001 47  64
91001 48  66
91001 49  48
91001 50  50
91001 51  65
91001 52  59
91001 53  66
91001 54  74
91001 55  80
91001 56  78
91001 57  84
91001 58  91
91001 59  91
91001 60  74
91001 61  48
91001 62  56
91001 63  66
91001 64  66
91001 65  80
91001 66  86
91001 67  79
91001 68  78
91001 69  73
91001 70  79
91001 71  68
91001 72  51
91001 73  35
91001 74  36
91001 75  48
91001 76  58
91001 77  61
91001 78  70
91001 79  77
91001 80  77
91001 81  58
91001 82  60
91001 83  52
91001 84  48
91001 85  40
91001 86  39
91001 87  40
91001 88  45
91001 89  47
91001 90  42
91001 91  43
91001 92  47
91001 93  54
91001 94  48
91001 95  37
91001 96  37
91001 97  41
91001 98  48
91001 99  52
91006  1  82
end

I need the Reshape to take the unique identifier of Regionname (aka ZipCode) into Variable names, then take dates starting from 2010m1 which is represented from X8. The method I'm using reshape seems to keep Regionname (Zip) all the way down. I instead would need each different zip as a different variable -- while all together vertically spanning the dates 2010m1 to date.

I'm pretty confident it's a quick fix in my code but any help is appreciated.

[Jean-Claude Arbaut]

If I understand your question correctly, you just have to do a further -reshape wide-:

reshape wide X, i(date) j(regionname)

[Nick Cox]

This produces a nice long layout. Note that the double rename is not needed. Why do you think you need a different layout?

* Example generated by -dataex-. To install: ssc install dataex
clear
input long regionname int(v8 v9 v10 v11 v12 v13 v14 v15 v16 v17)
91342 476 463 400 346 327 350 338 322 341 344
91335 357 318 297 255 242 233 231 230 237 265
91331 340 300 268 265 245 231 230 228 229 242
91801  54  54  55  57  61  64  59  66  70  74
91367 290 258 245 241 232 243 250 249 262 250
91406 288 247 230 200 188 190 186 182 185 189
91706 126 116 111 105 102 101 100 110 117 123
end

reshape long v , i(regionname) j(mdate) 
replace mdate = mdate - 8 + ym(2010, 1)
format mdate %tm
list, sepby(regionname) 

     +--------------------------+
     | region~e     mdate     v |
     |--------------------------|
  1. |    91331    2010m1   340 |
  2. |    91331    2010m2   300 |
  3. |    91331    2010m3   268 |
  4. |    91331    2010m4   265 |
  5. |    91331    2010m5   245 |
  6. |    91331    2010m6   231 |
  7. |    91331    2010m7   230 |
  8. |    91331    2010m8   228 |
  9. |    91331    2010m9   229 |
 10. |    91331   2010m10   242 |
     |--------------------------|
 11. |    91335    2010m1   357 |
 12. |    91335    2010m2   318 |
 13. |    91335    2010m3   297 |
 14. |    91335    2010m4   255 |
 15. |    91335    2010m5   242 |
 16. |    91335    2010m6   233 |
 17. |    91335    2010m7   231 |
 18. |    91335    2010m8   230 |
 19. |    91335    2010m9   237 |
 20. |    91335   2010m10   265 |
     |--------------------------|
 21. |    91342    2010m1   476 |
 22. |    91342    2010m2   463 |
 23. |    91342    2010m3   400 |
 24. |    91342    2010m4   346 |
 25. |    91342    2010m5   327 |
 26. |    91342    2010m6   350 |
 27. |    91342    2010m7   338 |
 28. |    91342    2010m8   322 |
 29. |    91342    2010m9   341 |
 30. |    91342   2010m10   344 |
     |--------------------------|
 31. |    91367    2010m1   290 |
 32. |    91367    2010m2   258 |
 33. |    91367    2010m3   245 |
 34. |    91367    2010m4   241 |
 35. |    91367    2010m5   232 |
 36. |    91367    2010m6   243 |
 37. |    91367    2010m7   250 |
 38. |    91367    2010m8   249 |
 39. |    91367    2010m9   262 |
 40. |    91367   2010m10   250 |
     |--------------------------|
 41. |    91406    2010m1   288 |
 42. |    91406    2010m2   247 |
 43. |    91406    2010m3   230 |
 44. |    91406    2010m4   200 |
 45. |    91406    2010m5   188 |
 46. |    91406    2010m6   190 |
 47. |    91406    2010m7   186 |
 48. |    91406    2010m8   182 |
 49. |    91406    2010m9   185 |
 50. |    91406   2010m10   189 |
     |--------------------------|
 51. |    91706    2010m1   126 |
 52. |    91706    2010m2   116 |
 53. |    91706    2010m3   111 |
 54. |    91706    2010m4   105 |
 55. |    91706    2010m5   102 |
 56. |    91706    2010m6   101 |
 57. |    91706    2010m7   100 |
 58. |    91706    2010m8   110 |
 59. |    91706    2010m9   117 |
 60. |    91706   2010m10   123 |
     |--------------------------|
 61. |    91801    2010m1    54 |
 62. |    91801    2010m2    54 |
 63. |    91801    2010m3    55 |
 64. |    91801    2010m4    57 |
 65. |    91801    2010m5    61 |
 66. |    91801    2010m6    64 |
 67. |    91801    2010m7    59 |
 68. |    91801    2010m8    66 |
 69. |    91801    2010m9    70 |
 70. |    91801   2010m10    74 |
     +--------------------------+

[Ali Sheikhpour]

Because there's no reason for regionname to contain it's own column. Regionname is the zip code corresponding to v (prices). So I think part of this may require going to wide. But I want to create a uniform timeline without repetitions such that every different "regionname" (zip code) is it's own column.

So 91331 would be just one column. 91335 another separate column. And the values in those columns with be those corresponding to the values in the variable v. That way the long format would have 1 mdate from 1/1/10 spanning to the end of the dataset with no repetition.

[Ali Sheikhpour]

If I understand your question correctly, you just have to do a further -reshape wide-:

reshape wide X, i(date) j(regionname)

I believe this may have solved it:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte month int(X91001 X91006 X91007 X91010 X91011 X91016 X91024 X91030 X91040 X91042 X91103 X91104 X91105 X91106 X91107 X91214)
 1 104  82  57 53  88  84 36 36 123  93  89  86  78  86  90  98
 2  98  86  62 55  76  94 36 43 129  99  86  85  70  96  96 102
 3 108 101  61 57  77 107 37 49 119  98  89  86  64  97  98  98
 4 120 104  72 58  84 118 50 52 114 100  91  96  72 106  97  97
 5 117 104  76 50  75 110 52 46 110  94  82  94  74 102  98  98
 6 119 115  85 53  81 122 52 41 114 102  79  96  86 114 110 108
 7 129 112  85 58  80 113 52 45 115 102  89  98  88 114 122 114
 8 128 118  88 64  75 120 51 48 120 101 100  90  84 112 127 103
 9 134 110  85 79  86 132 52 47 117 102 101  96  81 106 126 104
10 137 112  72 82  81 128 54 45 104 110  97  98  84 108 138  96
11 134 106  67 83  79 122 52 42 112 108  98  96  80 122 146  86
12 121  96  77 79  85 107 52 42 127 111  86  85  70 106 142  80
13 112  88  66 69  73 114 42 34 129 104  76  75  62  96 116  82
14 116  84  74 79  82 134 42 38 129 108  82  82  72 104 115  87
15 120 110  95 85  95 155 58 57 133 106  86  90  75 112 132  91
16 123 116 102 74 102 154 53 64 132 111  84 102  86 110 140 100
17 131 130 110 78 106 154 60 70 142 113  84 102  86 120 148 112
18 133 146 111 88 130 173 70 74 140 116  89 106  93 142 150 119
19 136 142 112 76 129 176 64 72 150 118  88 110  98 132 166 122
20 144 151 110 91 111 170 57 77 137 125  81 112  86 124 152 117
21 116 144 100 93  90 164 52 68 134 110  74 106  78 102 141 108
22 114 150 100 94  88 156 54 64 119 102  72 112  86 100 132 108
23 119 142  97 96  80 143 52 66 108 107  79 112  96  94 135 101
24 114 127  84 82  68 124 50 58  95 105  78  92  94  84 126  88
25 106 120  72 78  68 114 42 51  94  91  76  95  82  80 120  94
26 111 120  83 84  72 112 48 41  81  93  79 103  97  84 126  85
27 118 116  78 78  74 118 52 37  78  92  80 108  95  80 129  72
28 111 114  70 80  71 116 42 39  80  82  81  98  94  74 114  60
29 103 111  78 87  81 104 42 34  76  79  77  97  95  80 100  60
30  98 108  76 70  85 110 43 45  66  82  60  90 100  80 106  51
31  96 106  68 65  80 108 42 56  72  92  53  74  83  71 104  60
32  91 113  88 64  66 111 41 62  76  91  53  72  69  66 102  64
33  94  98  91 62  60  94 39 52  74  80  54  79  68  62  86  51
34  95  82  67 57  56  67 31 47  53  81  64  75  76  55  84  42
35 100  67  60 56  45  76 34 48  50  84  58  74  64  60  74  46
36  83  59  50 39  29  68 27 34  44  68  45  66  56  54  63  39
37  76  46  43 40  23  68 27 24  40  64  42  51  57  45  48  26
38  80  46  46 36  31  68 28 26  49  67  48  51  68  46  60  36
39  72  49  44 39  36  80 30 32  51  62  50  53  72  46  62  36
40  58  50  40 32  37  71 31 39  55  50  48  58  74  50  64  30
41  76  58  49 26  46  70 40 39  48  46  44  55  69  58  69  34
42  68  66  50 30  54  76 31 38  57  46  46  57  64  48  68  42
43  82  68  47 31  56  73 28 31  61  71  44  58  63  41  75  46
44  78  78  57 30  54  72 29 31  52  66  41  58  67  46  80  52
45  68  94  56 27  53  76 34 24  46  53  38  54  60  49  78  52
46  64  91  57 31  49  73 32 22  53  57  40  60  64  56  86  43
47  64  72  52 30  50  71 26 25  49  56  37  58  58  56  82  37
48  66  62  47 36  48  64 28 26  41  54  40  46  44  47  73  32
49  48  50  38 36  41  49 23 30  27  42  35  44  38  34  51  28
50  50  50  42 36  49  53 28 29  30  40  36  42  44  38  48  36
51  65  56  54 38  47  68 26 26  36  42  43  44  52  46  52  46
52  59  70  55 35  50  78 30 28  51  53  48  43  54  48  57  58
53  66  74  60 38  54  80 30 30  50  70  44  42  50  49  74  63
54  74  95  50 39  60  82 28 31  58  65  50  50  49  64  82  66
55  80 115  75 36  62  90 27 34  64  64  47  55  40  68  89  72
56  78 110  82 35  48  87 24 36  60  62  42  56  42  59  81  60
57  84 110  95 38  47  92 22 40  54  63  39  55  42  65  78  42
58  91 110  96 46  42  82 20 30  56  62  43  50  49  67  77  38
59  91 120 106 46  38  82 26 24  52  59  54  50  55  60  72  32
60  74 108  88 39  34  75 27 21  52  49  42  35  49  56  68  30
61  48 107  79 34  31  74 24 28  55  42  34  36  42  48  68  28
62  56 115  78 31  32  78 22 20  42  42  39  36  32  46  64  26
63  66 116  88 32  46  80 29 23  42  45  32  43  38  48  66  37
64  66 140 107 41  51  81 31 29  40  49  34  46  38  63  75  46
65  80 153 106 34  64  79 35 30  46  65  32  42  53  60  89  52
66  86 166 120 39  68  88 29 31  48  68  40  43  62  65  86  54
67  79 192 157 49  62 102 25 34  54  68  33  45  67  88  87  61
68  78 190 174 43  70  98 29 36  54  64  35  48  71  80  90  64
69  73 174 165 40  67  91 34 34  59  61  38  53  64  77  88  50
70  79 162 144 40  54  80 42 29  53  62  32  54  68  56  91  52
71  68 131 124 41  49  74 34 27  47  61  22  48  60  52  80  55
72  51 113  99 40  37  70 27 25  43  47  17  44  52  41  69  37
73  35 107  74 26  38  53 15 21  38  38  16  28  44  26  52  32
74  36 125  84 26  39  52 16 20  37  38  17  34  45  22  69  30
75  48 122  91 32  48  52 24 21  31  43  23  37  50  26  71  39
76  58 126 100 30  62  61 24 18  26  40  32  44  56  37  72  42
77  61 126 111 29  67  58 21 26  30  46  32  50  63  40  73  41
78  70 151 115 29  70  66 27 28  43  49  36  51  69  40  81  51
79  77 158 132 41  72  70 29 38  54  56  46  58  70  40  95  58
80  77 166 136 45  61  76 28 32  62  63  41  58  73  41  81  62
81  58 145 119 44  54  74 22 31  64  62  38  50  64  47  81  64
82  60 129 112 40  56  66 24 28  62  59  38  48  60  43  87  60
83  52 113  97 32  44  60 18 29  58  55  39  40  48  37  80  52
84  48 100  88 28  34  51 21 23  52  44  39  30  43  29  63  48
85  40  97  78 26  30  44 18 19  38  30  36  26  42  25  60  38
86  39 111  78 25  40  46 20 25  36  27  26  28  44  34  62  48
87  40 128  81 26  44  47 17 18  39  30  31  33  53  43  64  43
88  45 124  88 26  55  46 24 21  36  38  33  32  58  50  66  39
89  47 136  80 24  52  42 26 23  38  46  29  35  67  45  72  46
90  42 132  90 29  55  52 24 23  32  46  40  30  64  40  70  54
91  43 133  92 34  50  50 22 23  32  40  34  29  56  47  60  50
92  47 127 101 31  47  53 19 15  35  40  31  31  53  37  63  46
93  54 132 106 30  46  53 18 16  38  44  26  28  51  42  60  37
94  48 114 100 20  39  46 21 18  42  36  24  28  58  38  55  44
95  37 115  79 22  35  45 23 14  36  30  22  30  59  34  53  42
96  37  98  63 22  26  35 20 10  35  21  21  18  46  28  46  30
97  41  92  67 23  26  30 17 12  29  18  21  25  35  27  45  25
98  48 122  79 24  35  30 16 13  35  22  24  20  43  36  54  30
99  52 126  90 20  39  34 18 13  34  23  28  28  44  40  56  27
end
format %tm month

------------------ copy up to and including the prev


So this would require two reshape's? Going to long then again back to wide?

[Nick Cox]

I want to create a uniform timeline without repetitions such that every different "regionname" (zip code) is its own column.

Yes, it's evident that you are asking to do that. It's not at all clear what use it will be for later work in Stata. (I am travelling now without computer access, so will be off-forum for a while.)