Create a panel data

[kotey dzanie]

I am trying to create panel data with the country in one column, year in another column, and then each variable under the Table description in a different column.

Used this code, but the layout of the panel is completely different than what I just described

gen id = _n
order id
reshape long x , i (id) j(year)
rename x value

* Example generated by -dataex-. For more info, type help dataex
clear
input str34 Country str48 TableDescription str17(x1990 x1991 x1992 x1993 x1994)
"Afghanistan"         "Establishments"                                   "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Employees"                                        "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Female employees"                                 "3628"              "2551"              "..."               "..."               "..."              
"Afghanistan"         "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Albania"             "Establishments"                                   "..."               "..."               "..."               "442"               "353"              
"Albania"             "Employees"                                        "252071"            "..."               "..."               "59031"             "36644"            
"Albania"             "Wages and salaries"                               "180069662.9213483" "..."               "..."               "23786493.57011635" "29441807.97351537"
"Albania"             "Output"                                           "..."               "..."               "..."               "220740654.0110227" "173356945.6911859"
"Albania"             "Value added"                                      "505393258.4269663" "..."               "..."               "82266852.37966932" "64607628.91326181"
"Albania"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Albania"             "Female employees"                                 "..."               "..."               "..."               "28463"             "15804"            
"Albania"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Algeria"             "Establishments"                                   "21985"             "22255"             "20213"             "..."               "25913"            
"Algeria"             "Employees"                                        "434885"            "450405"            "426508"            "425352"            "424200"           
"Algeria"             "Wages and salaries"                               "2427125937.258443" "1495868942.977203" "1609538344.23082"  "1740112733.952336" "1339332791.210896"
"Algeria"             "Output"                                           "12773641954.88299" "9043908974.645256" "9641064156.447531" "10687211407.36591" "8434102758.700379"
"Algeria"             "Value added"                                      "5556567741.831768" "3831780380.693314" "4347530405.533046" "4740958253.587097" "3680647954.685798"
"Algeria"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Algeria"             "Female employees"                                 "48004"             "45833"             "40631"             "..."               "..."              
"Algeria"             "Index numbers of industrial production(2015=100)" "195.131422821951"  "185.065110610373"  "173.450145323416"  "170.739983814001"  "155.446957994722" 
"Angola"              "Establishments"                                   "..."               "..."               "171"               "171"               "..."              
"Angola"              "Employees"                                        "..."               "23598"             "18161"             "7524"              "..."              
"Angola"              "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Angola"              "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Angola"              "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Angola"              "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Angola"              "Female employees"                                 "..."               "..."               "2944"              "1466"              "..."              
"Angola"              "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Establishments"                                   "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Employees"                                        "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Argentina"           "Establishments"                                   "..."               "..."               "..."               "90088"             "..."              
"Argentina"           "Employees"                                        "941855"            "..."               "..."               "858994"            "756685"           
"Argentina"           "Wages and salaries"                               "6373150337.681941" "..."               "..."               "12414812212.07152" "9563250744.738781"
"Argentina"           "Output"                                           "79001257206.37668" "..."               "..."               "90557490595.08722" "90353762932.82936"
"Argentina"           "Value added"                                      "31156363248.55565" "..."               "..."               "29818829045.81429" "33831655001.76175"
"Argentina"           "Gross fixed capital formation"                    "..."               "..."               "..."               "3734369024.9523"   "..."              
"Argentina"           "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Argentina"           "Index numbers of industrial production(2015=100)" "51.4225944903331"  "56.6508903794229"  "63.9969745890228"  "66.1809478878405"  "69.2252704979669" 
"Armenia"             "Establishments"                                   "675"               "704"               "690"               "719"               "732"              
"Armenia"             "Employees"                                        "354424"            "323832"            "307921"            "281309"            "273252"           
"Armenia"             "Wages and salaries"                               "..."               "..."               "..."               "..."               "23873826.82522867"
"Armenia"             "Output"                                           "..."               "..."               "..."               "..."               "261290325.1174751"
"Armenia"             "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Establishments"                                   "..."               "..."               "..."               "86"                "98"               
"Aruba"               "Employees"                                        "..."               "..."               "..."               "1719"              "1779"             
"Aruba"               "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Australia"           "Establishments"                                   "30474"             "40653"             "41480"             "42707"             "44585"            
"Australia"           "Employees"                                        "1016900"           "1079000"           "1009000"           "914835"            "911132"           
"Australia"           "Wages and salaries"                               "21051444237.06361" "23862011160.99944" "22253181438.9622"  "17280456989.17419" "19555346810.93269"
"Australia"           "Output"                                           "128877169399.9564" "132959845952.0345" "122544152380.0571" "118076107061.2556" "134421396877.0632"
"Australia"           "Value added"                                      "54052239673.95674" "55777733463.36843" "51408265555.4152"  "42227246763.13786" "46978812016.95338"
"Australia"           "Gross fixed capital formation"                    "5443941994.774628" "8344264797.983418" "8289220121.499829" "..."               "..."              
"Australia"           "Female employees"                                 "270548"            "..."               "..."               "..."               "..."              
"Australia"           "Index numbers of industrial production(2015=100)" "83.2272261393788"  "80.2811296388698"  "79.42185149288809" "82.8344132726443"  "86.6397879191351" 
"Austria"             "Establishments"                                   "9144"              "8920"              "8598"              "8385"              "8185"             
"Austria"             "Employees"                                        "646501"            "628960"            "596088"            "559957"            "553754"           
"Austria"             "Wages and salaries"                               "16484296850.45469" "17079508982.67812" "18343339911.29765" "17143068132.02673" "17639035062.69456"
"Austria"             "Output"                                           "90475437081.70857" "91864155046.06834" "96350497568.32359" "87700452305.80687" "93989906930.37578"
"Austria"             "Value added"                                      "31317806240.75674" "31872645992.67904" "33641984758.82328" "30934193343.77484" "33371674052.11215"
"Austria"             "Gross fixed capital formation"                    "5889140591.907324" "5987619589.120848" "6424328826.00867"  "5080516052.636559" "5027433992.405332"
"Austria"             "Female employees"                                 "188882"            "181380"            "169674"            "155763"            "151504"           
"Austria"             "Index numbers of industrial production(2015=100)" "45.9250266447737"  "47.4405539477271"  "46.9813036812794"  "46.0168752743875"  "49.2316271395216" 
"Azerbaijan"          "Establishments"                                   "3663"              "3542"              "4989"              "5894"              "3863"             
"Azerbaijan"          "Employees"                                        "358380"            "345053"            "313856"            "285146"            "272606"           
"Azerbaijan"          "Wages and salaries"                               "111397.5903614458" "105950.6578947368" "25272970.4797048"  "102264066.0165041" "37514655.54299543"
"Azerbaijan"          "Output"                                           "1330361.445783132" "1486513.157894737" "346669741.697417"  "995247811.9529883" "531861357.4487733"
"Azerbaijan"          "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Index numbers of industrial production(2015=100)" "164.548968889397"  "148.094072000457"  "100.111592672309"  "80.28949732319199" "58.2098855593142" 
"Bahamas"             "Establishments"                                   "256"               "205"               "222"               "..."               "..."              
"Bahamas"             "Employees"                                        "2309"              "2182"              "2153"              "..."               "..."              
"Bahamas"             "Wages and salaries"                               "37516500"          "57863200"          "44716600"          "..."               "..."              
"Bahamas"             "Output"                                           "172924700"         "190733400"         "186493300"         "..."               "..."              
"Bahamas"             "Value added"                                      "79162800"          "96073800"          "95285200"          "..."               "..."              
"Bahamas"             "Gross fixed capital formation"                    "27447400"          "14666100"          "21790300"          "..."               "..."              
"Bahamas"             "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Bahamas"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Bahrain"             "Establishments"                                   "..."               "..."               "2276"              "..."               "..."              
"Bahrain"             "Employees"                                        "..."               "..."               "28285"             "..."               "..."              
"Bahrain"             "Wages and salaries"                               "..."               "..."               "273026595.7446808" "..."               "..."              
"Bahrain"             "Output"                                           "..."               "..."               "1729906914.893617" "3136537670.212766" "3581908050.531915"
end

[Nick Cox]

That's a horrible data structure, but the problem is programmable. Here is some technique that may help. Note that you may wish to rename some of the variables at the end.

The key is that from the existing variables you need reshape long to cope with the different years but you also need reshape wide to cope with different variables otherwise.

* Example generated by -dataex-. For more info, type help dataex
clear
input str34 Country str48 TableDescription str17(x1990 x1991 x1992 x1993 x1994)
"Afghanistan"         "Establishments"                                   "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Employees"                                        "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Afghanistan"         "Female employees"                                 "3628"              "2551"              "..."               "..."               "..."              
"Afghanistan"         "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Albania"             "Establishments"                                   "..."               "..."               "..."               "442"               "353"              
"Albania"             "Employees"                                        "252071"            "..."               "..."               "59031"             "36644"            
"Albania"             "Wages and salaries"                               "180069662.9213483" "..."               "..."               "23786493.57011635" "29441807.97351537"
"Albania"             "Output"                                           "..."               "..."               "..."               "220740654.0110227" "173356945.6911859"
"Albania"             "Value added"                                      "505393258.4269663" "..."               "..."               "82266852.37966932" "64607628.91326181"
"Albania"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Albania"             "Female employees"                                 "..."               "..."               "..."               "28463"             "15804"            
"Albania"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Algeria"             "Establishments"                                   "21985"             "22255"             "20213"             "..."               "25913"            
"Algeria"             "Employees"                                        "434885"            "450405"            "426508"            "425352"            "424200"           
"Algeria"             "Wages and salaries"                               "2427125937.258443" "1495868942.977203" "1609538344.23082"  "1740112733.952336" "1339332791.210896"
"Algeria"             "Output"                                           "12773641954.88299" "9043908974.645256" "9641064156.447531" "10687211407.36591" "8434102758.700379"
"Algeria"             "Value added"                                      "5556567741.831768" "3831780380.693314" "4347530405.533046" "4740958253.587097" "3680647954.685798"
"Algeria"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Algeria"             "Female employees"                                 "48004"             "45833"             "40631"             "..."               "..."              
"Algeria"             "Index numbers of industrial production(2015=100)" "195.131422821951"  "185.065110610373"  "173.450145323416"  "170.739983814001"  "155.446957994722" 
"Angola"              "Establishments"                                   "..."               "..."               "171"               "171"               "..."              
"Angola"              "Employees"                                        "..."               "23598"             "18161"             "7524"              "..."              
"Angola"              "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Angola"              "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Angola"              "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Angola"              "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Angola"              "Female employees"                                 "..."               "..."               "2944"              "1466"              "..."              
"Angola"              "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Establishments"                                   "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Employees"                                        "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Antigua and Barbuda" "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Argentina"           "Establishments"                                   "..."               "..."               "..."               "90088"             "..."              
"Argentina"           "Employees"                                        "941855"            "..."               "..."               "858994"            "756685"           
"Argentina"           "Wages and salaries"                               "6373150337.681941" "..."               "..."               "12414812212.07152" "9563250744.738781"
"Argentina"           "Output"                                           "79001257206.37668" "..."               "..."               "90557490595.08722" "90353762932.82936"
"Argentina"           "Value added"                                      "31156363248.55565" "..."               "..."               "29818829045.81429" "33831655001.76175"
"Argentina"           "Gross fixed capital formation"                    "..."               "..."               "..."               "3734369024.9523"   "..."              
"Argentina"           "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Argentina"           "Index numbers of industrial production(2015=100)" "51.4225944903331"  "56.6508903794229"  "63.9969745890228"  "66.1809478878405"  "69.2252704979669" 
"Armenia"             "Establishments"                                   "675"               "704"               "690"               "719"               "732"              
"Armenia"             "Employees"                                        "354424"            "323832"            "307921"            "281309"            "273252"           
"Armenia"             "Wages and salaries"                               "..."               "..."               "..."               "..."               "23873826.82522867"
"Armenia"             "Output"                                           "..."               "..."               "..."               "..."               "261290325.1174751"
"Armenia"             "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Armenia"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Establishments"                                   "..."               "..."               "..."               "86"                "98"               
"Aruba"               "Employees"                                        "..."               "..."               "..."               "1719"              "1779"             
"Aruba"               "Wages and salaries"                               "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Output"                                           "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Aruba"               "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Australia"           "Establishments"                                   "30474"             "40653"             "41480"             "42707"             "44585"            
"Australia"           "Employees"                                        "1016900"           "1079000"           "1009000"           "914835"            "911132"           
"Australia"           "Wages and salaries"                               "21051444237.06361" "23862011160.99944" "22253181438.9622"  "17280456989.17419" "19555346810.93269"
"Australia"           "Output"                                           "128877169399.9564" "132959845952.0345" "122544152380.0571" "118076107061.2556" "134421396877.0632"
"Australia"           "Value added"                                      "54052239673.95674" "55777733463.36843" "51408265555.4152"  "42227246763.13786" "46978812016.95338"
"Australia"           "Gross fixed capital formation"                    "5443941994.774628" "8344264797.983418" "8289220121.499829" "..."               "..."              
"Australia"           "Female employees"                                 "270548"            "..."               "..."               "..."               "..."              
"Australia"           "Index numbers of industrial production(2015=100)" "83.2272261393788"  "80.2811296388698"  "79.42185149288809" "82.8344132726443"  "86.6397879191351" 
"Austria"             "Establishments"                                   "9144"              "8920"              "8598"              "8385"              "8185"             
"Austria"             "Employees"                                        "646501"            "628960"            "596088"            "559957"            "553754"           
"Austria"             "Wages and salaries"                               "16484296850.45469" "17079508982.67812" "18343339911.29765" "17143068132.02673" "17639035062.69456"
"Austria"             "Output"                                           "90475437081.70857" "91864155046.06834" "96350497568.32359" "87700452305.80687" "93989906930.37578"
"Austria"             "Value added"                                      "31317806240.75674" "31872645992.67904" "33641984758.82328" "30934193343.77484" "33371674052.11215"
"Austria"             "Gross fixed capital formation"                    "5889140591.907324" "5987619589.120848" "6424328826.00867"  "5080516052.636559" "5027433992.405332"
"Austria"             "Female employees"                                 "188882"            "181380"            "169674"            "155763"            "151504"           
"Austria"             "Index numbers of industrial production(2015=100)" "45.9250266447737"  "47.4405539477271"  "46.9813036812794"  "46.0168752743875"  "49.2316271395216" 
"Azerbaijan"          "Establishments"                                   "3663"              "3542"              "4989"              "5894"              "3863"             
"Azerbaijan"          "Employees"                                        "358380"            "345053"            "313856"            "285146"            "272606"           
"Azerbaijan"          "Wages and salaries"                               "111397.5903614458" "105950.6578947368" "25272970.4797048"  "102264066.0165041" "37514655.54299543"
"Azerbaijan"          "Output"                                           "1330361.445783132" "1486513.157894737" "346669741.697417"  "995247811.9529883" "531861357.4487733"
"Azerbaijan"          "Value added"                                      "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Gross fixed capital formation"                    "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Azerbaijan"          "Index numbers of industrial production(2015=100)" "164.548968889397"  "148.094072000457"  "100.111592672309"  "80.28949732319199" "58.2098855593142" 
"Bahamas"             "Establishments"                                   "256"               "205"               "222"               "..."               "..."              
"Bahamas"             "Employees"                                        "2309"              "2182"              "2153"              "..."               "..."              
"Bahamas"             "Wages and salaries"                               "37516500"          "57863200"          "44716600"          "..."               "..."              
"Bahamas"             "Output"                                           "172924700"         "190733400"         "186493300"         "..."               "..."              
"Bahamas"             "Value added"                                      "79162800"          "96073800"          "95285200"          "..."               "..."              
"Bahamas"             "Gross fixed capital formation"                    "27447400"          "14666100"          "21790300"          "..."               "..."              
"Bahamas"             "Female employees"                                 "..."               "..."               "..."               "..."               "..."              
"Bahamas"             "Index numbers of industrial production(2015=100)" "..."               "..."               "..."               "..."               "..."              
"Bahrain"             "Establishments"                                   "..."               "..."               "2276"              "..."               "..."              
"Bahrain"             "Employees"                                        "..."               "..."               "28285"             "..."               "..."              
"Bahrain"             "Wages and salaries"                               "..."               "..."               "273026595.7446808" "..."               "..."              
"Bahrain"             "Output"                                           "..."               "..."               "1729906914.893617" "3136537670.212766" "3581908050.531915"
end

egen which = group(TableDescription), label 
drop TableDescription
su which, meanonly 
local J = r(max)
forval j = 1/`J' { 
    local label`j' : label (which) `j'
}
reshape long x, i(Country which) j(year)
replace x = "." if x == "..."
reshape wide x, i(Country year) j(which)

forval j = 1/`J' {
    label var x`j' "`label`j''"
}

destring, replace 
describe 
list

. describe 

Contains data
 Observations:            65                  
    Variables:            10                  
------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
------------------------------------------------------------------------------------
Country         str34   %34s                  
year            int     %10.0g                
x1              long    %10.0g                Employees
x2              long    %10.0g                Establishments
x3              long    %10.0g                Female employees
x4              double  %10.0g                Gross fixed capital formation
x5              double  %10.0g                Index numbers of industrial
                                                production(2015=100)
x6              double  %10.0g                Output
x7              double  %10.0g                Value added
x8              double  %10.0g                Wages and salaries
------------------------------------------------------------------------------------
Sorted by: Country  year

[kotey dzanie]

Thank you Nick Cox