Converting columnwise data into a panel data

[Itesh New]

Hello Stata users, I was wondering how to convert the data which has column wise id into a panel data , so I want to stack the columns for example I want andhra_cpi_d11 below andaman_cpi_d11, similarly arunachal_cpi_d11 below andhra_cpi_d11 and same is the case for assam_cpi_d11 forming a panel data wherein a single column name "state" has rows starting from andaman_cpi_d11 to assam_cpi_d11 stacked over each other and we have the respective date column as well.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 date float(andaman_cpi_d11 andhra_cpi_d11 arunachal_cpi_d11 assam_cpi_d11)
"2011M01" 93.52125 91.08701 87.87403 87.54991
"2011M02" 93.77636 89.15061 88.18394 87.94121
"2011M03" 94.31012  89.2463 88.31461  88.3741
"2011M04" 94.33296 89.48104 88.24075 89.55927
"2011M05" 94.87976 90.16936 89.14168 90.25575
"2011M06"  95.3646 90.13373 89.23786 91.02339
"2011M07" 96.04561 92.06562 90.06701 91.60116
"2011M08" 96.41101 92.10646 91.01899 92.58604
"2011M09" 96.67253 93.25573 92.45681 93.27772
"2011M10" 97.37081 94.51428 92.88341 93.67498
"2011M11" 99.37086 94.76704 93.15155 94.15209
"2011M12"  95.4293 95.07026 93.76149 95.02677
"2012M01" 97.92684 95.83161 95.26787 95.51166
"2012M02" 98.22737 96.61899 95.13788 96.54797
"2012M03" 98.41333 97.84368 95.97744 97.49464
"2012M04"  98.8379 98.78738 97.19098 98.99621
"2012M05" 99.62144 98.91296 98.44595 100.3349
"2012M06" 101.0151  99.5423 99.94466 100.2469
"2012M07" 99.77572 100.2128 100.6055 101.0948
"2012M08" 99.31303 100.8303 101.7677  101.132
"2012M09" 101.2525 101.5133 102.8612 101.3499
"2012M10" 101.8755 102.6004 104.3335 102.3059
"2012M11" 102.2942 103.3087 105.2949 102.9071
"2012M12" 103.3694 105.0754  106.401 103.8137
"2013M01" 103.8485 105.8885 107.1993 104.7255
"2013M02" 104.4352 107.3308 107.7998 105.5774
"2013M03" 104.6219 107.4499 108.6356 106.4837
"2013M04"  104.116 108.0769 108.9549 105.8084
"2013M05" 105.3544 109.0908  109.222 106.5477
"2013M06" 108.1462 110.2991 110.2284 107.8454
"2013M07" 107.9395 111.1691 111.6431 108.7042
"2013M08" 108.2352 112.6463 112.6762 109.8843
"2013M09" 107.9719 113.7759 113.2489 111.2303
"2013M10" 108.6313 114.1944 113.9182 112.4172
"2013M11" 108.9415  114.782 115.6725 114.8921
"2013M12"  109.647 114.7896 115.0252 114.6913
"2014M01" 110.0331 114.4048 115.3779 115.3374
"2014M02" 110.2524  114.859 116.5768 115.6283
"2014M03"   111.14 115.6195 116.7546 116.4803
"2014M04" 112.4145 116.0421  119.555 116.1282
"2014M05"  113.653  116.594  118.752 116.3666
"2014M06" 113.7115 116.2258 119.5232 117.4318
"2014M07" 113.6952 119.0503 120.0623 118.4913
"2014M08" 114.6066 119.4466 120.4913 118.7524
"2014M09" 115.8773  118.436 121.5491 119.4737
"2014M10" 115.8309 118.5783 121.7381 118.9463
"2014M11" 116.5425 119.0847 122.4282 118.7882
"2014M12" 116.8384 119.4668 124.0093 118.3091
"2015M01" 117.3471 120.6734 124.7754 118.1527
"2015M02" 117.6853 121.8347 125.4969 119.5335
"2015M03" 118.8785 122.5634   126.61 120.0753
"2015M04" 118.7764 123.1613 127.2047  121.145
"2015M05" 118.2432 123.6319 128.5269 122.2394
"2015M06" 118.8866 124.7021 131.1479 122.9424
"2015M07" 120.4216 124.9816 130.0885 122.7719
"2015M08" 121.8901 126.1602 130.6057 123.2621
"2015M09" 123.0172  127.118 131.0245 124.3372
"2015M10" 125.5734 128.2875 131.1139 124.5465
"2015M11" 124.7267 129.6957  131.482 124.8324
"2015M12" 124.8689 130.7381 132.2415 125.1856
"2016M01" 124.8666  131.926 133.8343 125.2291
"2016M02" 126.7619 131.5585 135.4292 126.0323
"2016M03" 127.1142 131.9971 136.4271  126.348
"2016M04" 128.3104 133.0727 137.6145 126.7415
"2016M05" 129.5802 134.6052 138.4836 127.0885
"2016M06" 130.6752 134.8264 137.6913 127.0616
"2016M07" 130.1482 134.3725 138.7104 127.1652
"2016M08" 129.9264 134.2295 138.9294 127.4972
"2016M09" 129.5586 134.3488 138.6918  127.101
"2016M10" 130.1538 135.1157 139.1662 127.3556
"2016M11" 128.9597 133.8096   139.38  127.237
"2016M12" 131.0009 133.8558 139.6018 127.4221
"2017M01" 132.8689 134.2809 138.9247 127.6517
"2017M02" 133.6283 135.7115 140.2868 127.6064
"2017M03" 132.1858 136.0244 141.0324   128.12
"2017M04" 131.6172 135.8718 141.0787 128.8501
"2017M05" 132.4052 135.6324 142.0499 128.9866
"2017M06" 132.5891  135.776  142.077 129.4744
"2017M07" 133.7328  137.865 141.4346 129.9091
"2017M08" 134.6093 138.0322  141.963 131.3296
"2017M09" 134.9625 138.0121 142.7084 131.9634
"2017M10" 135.3868 140.0565 144.7417  132.292
"2017M11"  136.374 142.0255 146.1328   134.56
"2017M12" 136.8332 142.2743 146.7943 136.1706
"2018M01" 138.2325  141.735  148.147 136.9221
"2018M02" 138.8243 140.7933 148.6615 137.3275
"2018M03" 138.5766  141.042 149.3914 137.8911
"2018M04" 140.6308 140.9939 150.9299 138.3333
"2018M05" 141.0139 141.2215 151.3957 138.1181
"2018M06"  141.401 140.5004  152.599 138.5163
"2018M07" 142.3081 141.7281 155.0603 139.0572
"2018M08" 140.9081  142.318 156.5998  139.293
"2018M09" 140.7706 140.6541 159.6886 139.6285
"2018M10" 144.0193 138.5512 159.7878 140.2272
"2018M11"  148.467 138.8025 163.0128 141.6058
"2018M12" 147.6485 139.2486 161.3834 142.5141
"2019M01" 148.9001 140.0509 160.4285 143.3586
"2019M02" 149.8563 141.3891 159.8405  144.823
"2019M03" 150.5564 141.8714 159.0665 145.1924
"2019M04" 149.8636 142.1058 157.8211 144.9478
end

[Nick Cox]

Here is one way to do it. https://www.stata.com/support/faqs/d...-with-reshape/ underlines that it often helps to rename variables when their name structure is backwards. There is another syntax, but reshape is easiest whenever names share a common prefix -- not a common suffix.

rename (*_cpi_d11) (_cpi_d11*)
reshape long _cpi_d11 , i(date) j(region) string
gen mdate = monthly(date, "YM")
format mdate %tm

[Itesh New]

Nick Cox ,it worked and thank you for your help, really appreciate it.