Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Reshape IMF data

    I want to generate a variable year by making y* long. Then reshape SubjectDescriptor as wide i.e. all the categories of SubjectDescriptor will become variable names.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input int WEOCountryCode str3 ISO str12 WEOSubjectCode str32 country str82 SubjectDescriptor str8 Scale str22(y2000 y2001 y2002 y2003 y2004)
512 "AFG" "NGDP_R"       "Afghanistan" "Gross domestic product, constant prices"                                            "Billions" "n/a"         "n/a"         "182.842"     "198.736"     "200.069"    
512 "AFG" "NGDP_RPCH"    "Afghanistan" "Gross domestic product, constant prices"                                            ""         "n/a"         "n/a"         "n/a"         "8.692"       "0.671"      
512 "AFG" "NGDP"         "Afghanistan" "Gross domestic product, current prices"                                             "Billions" "n/a"         "n/a"         "178.756"     "220.013"     "246.210"    
512 "AFG" "NGDPD"        "Afghanistan" "Gross domestic product, current prices"                                             "Billions" "n/a"         "n/a"         "4.367"       "4.553"       "5.146"      
512 "AFG" "PPPGDP"       "Afghanistan" "Gross domestic product, current prices"                                             "Billions" "n/a"         "n/a"         "18.835"      "20.852"      "21.557"     
512 "AFG" "NGDP_D"       "Afghanistan" "Gross domestic product, deflator"                                                   ""         "n/a"         "n/a"         "97.765"      "110.706"     "123.063"    
512 "AFG" "NGDPRPC"      "Afghanistan" "Gross domestic product per capita, constant prices"                                 "Units"    "n/a"         "n/a"         "8,235.103"   "8,597.264"   "8,329.717"  
512 "AFG" "NGDPRPPPPC"   "Afghanistan" "Gross domestic product per capita, constant prices"                                 "Units"    "n/a"         "n/a"         "1,026.919"   "1,072.081"   "1,038.717"  
512 "AFG" "NGDPPC"       "Afghanistan" "Gross domestic product per capita, current prices"                                  "Units"    "n/a"         "n/a"         "8,051.067"   "9,517.718"   "10,250.788" 
512 "AFG" "NGDPDPC"      "Afghanistan" "Gross domestic product per capita, current prices"                                  "Units"    "n/a"         "n/a"         "196.683"     "196.954"     "214.230"    
512 "AFG" "PPPPC"        "Afghanistan" "Gross domestic product per capita, current prices"                                  "Units"    "n/a"         "n/a"         "848.304"     "902.056"     "897.513"    
512 "AFG" "NGAP_NPGDP"   "Afghanistan" "Output gap in percent of potential GDP"                                             ""         ""            ""            ""            ""            ""           
512 "AFG" "PPPSH"        "Afghanistan" "Gross domestic product based on purchasing-power-parity (PPP) share of world total" ""         "n/a"         "n/a"         "0.034"       "0.036"       "0.034"      
512 "AFG" "PPPEX"        "Afghanistan" "Implied PPP conversion rate"                                                        ""         "n/a"         "n/a"         "9.491"       "10.551"      "11.421"     
512 "AFG" "NID_NGDP"     "Afghanistan" "Total investment"                                                                   ""         "n/a"         "n/a"         "27.243"      "30.103"      "35.354"     
512 "AFG" "NGSD_NGDP"    "Afghanistan" "Gross national savings"                                                             ""         "n/a"         "n/a"         "61.151"      "59.719"      "72.570"     
512 "AFG" "PCPI"         "Afghanistan" "Inflation, average consumer prices"                                                 ""         "n/a"         "n/a"         "31.263"      "42.413"      "49.351"     
512 "AFG" "PCPIPCH"      "Afghanistan" "Inflation, average consumer prices"                                                 ""         "n/a"         "n/a"         "n/a"         "35.663"      "16.358"     
512 "AFG" "PCPIE"        "Afghanistan" "Inflation, end of period consumer prices"                                           ""         "n/a"         "n/a"         "42.890"      "45.691"      "51.752"     
512 "AFG" "PCPIEPCH"     "Afghanistan" "Inflation, end of period consumer prices"                                           ""         "n/a"         "n/a"         "n/a"         "6.530"       "13.266"     
512 "AFG" "FLIBOR6"      "Afghanistan" "Six-month London interbank offered rate (LIBOR)"                                    ""         ""            ""            ""            ""            ""           
512 "AFG" "TM_RPCH"      "Afghanistan" "Volume of imports of goods and services"                                            ""         "n/a"         "n/a"         "n/a"         "34.585"      "0.844"      
512 "AFG" "TMG_RPCH"     "Afghanistan" "Volume of Imports of goods"                                                         ""         "n/a"         "n/a"         "n/a"         "33.828"      "-1.249"     
512 "AFG" "TX_RPCH"      "Afghanistan" "Volume of exports of goods and services"                                            ""         "n/a"         "n/a"         "n/a"         "48.778"      "-9.071"     
512 "AFG" "TXG_RPCH"     "Afghanistan" "Volume of exports of goods"                                                         ""         "n/a"         "n/a"         "n/a"         "90.090"      "-28.746"    
512 "AFG" "LUR"          "Afghanistan" "Unemployment rate"                                                                  ""         ""            ""            ""            ""            ""           
512 "AFG" "LE"           "Afghanistan" "Employment"                                                                         "Millions" ""            ""            ""            ""            ""           
512 "AFG" "LP"           "Afghanistan" "Population"                                                                         "Millions" "n/a"         "n/a"         "22.203"      "23.116"      "24.019"     
512 "AFG" "GGR"          "Afghanistan" "General government revenue"                                                         "Billions" "n/a"         "n/a"         "12.235"      "21.618"      "31.209"     
512 "AFG" "GGR_NGDP"     "Afghanistan" "General government revenue"                                                         ""         "n/a"         "n/a"         "6.845"       "9.826"       "12.676"     
512 "AFG" "GGX"          "Afghanistan" "General government total expenditure"                                               "Billions" "n/a"         "n/a"         "12.411"      "26.242"      "37.101"     
512 "AFG" "GGX_NGDP"     "Afghanistan" "General government total expenditure"                                               ""         "n/a"         "n/a"         "6.943"       "11.927"      "15.069"     
512 "AFG" "GGXCNL"       "Afghanistan" "General government net lending/borrowing"                                           "Billions" "n/a"         "n/a"         "-0.176"      "-4.624"      "-5.891"     
512 "AFG" "GGXCNL_NGDP"  "Afghanistan" "General government net lending/borrowing"                                           ""         "n/a"         "n/a"         "-0.098"      "-2.102"      "-2.393"     
512 "AFG" "GGSB"         "Afghanistan" "General government structural balance"                                              "Billions" ""            ""            ""            ""            ""           
512 "AFG" "GGSB_NPGDP"   "Afghanistan" "General government structural balance"                                              ""         ""            ""            ""            ""            ""           
512 "AFG" "GGXONLB"      "Afghanistan" "General government primary net lending/borrowing"                                   "Billions" "n/a"         "n/a"         "n/a"         "n/a"         "-5.891"     
512 "AFG" "GGXONLB_NGDP" "Afghanistan" "General government primary net lending/borrowing"                                   ""         "n/a"         "n/a"         "n/a"         "n/a"         "-2.393"     
512 "AFG" "GGXWDN"       "Afghanistan" "General government net debt"                                                        "Billions" ""            ""            ""            ""            ""           
512 "AFG" "GGXWDN_NGDP"  "Afghanistan" "General government net debt"                                                        ""         ""            ""            ""            ""            ""           
512 "AFG" "GGXWDG"       "Afghanistan" "General government gross debt"                                                      "Billions" "n/a"         "n/a"         "618.456"     "595.360"     "603.134"    
512 "AFG" "GGXWDG_NGDP"  "Afghanistan" "General government gross debt"                                                      ""         "n/a"         "n/a"         "345.977"     "270.602"     "244.967"    
512 "AFG" "NGDP_FY"      "Afghanistan" "Gross domestic product corresponding to fiscal year, current prices"                "Billions" "n/a"         "n/a"         "178.756"     "220.013"     "246.210"    
512 "AFG" "BCA"          "Afghanistan" "Current account balance"                                                            "Billions" "n/a"         "n/a"         "1.481"       "1.348"       "1.915"      
512 "AFG" "BCA_NGDPD"    "Afghanistan" "Current account balance"                                                            ""         "n/a"         "n/a"         "33.908"      "29.616"      "37.216"     
914 "ALB" "NGDP_R"       "Albania"     "Gross domestic product, constant prices"                                            "Billions" "391.330"     "423.784"     "443.009"     "467.501"     "493.283"    
914 "ALB" "NGDP_RPCH"    "Albania"     "Gross domestic product, constant prices"                                            ""         "6.946"       "8.293"       "4.537"       "5.529"       "5.515"      
914 "ALB" "NGDP"         "Albania"     "Gross domestic product, current prices"                                             "Billions" "501.199"     "563.449"     "610.494"     "677.738"     "737.656"    
914 "ALB" "NGDPD"        "Albania"     "Gross domestic product, current prices"                                             "Billions" "3.483"       "3.928"       "4.348"       "5.611"       "7.185"      
914 "ALB" "PPPGDP"       "Albania"     "Gross domestic product, current prices"                                             "Billions" "12.908"      "14.285"      "15.169"      "16.305"      "17.668"     
914 "ALB" "NGDP_D"       "Albania"     "Gross domestic product, deflator"                                                   ""         "128.076"     "132.957"     "137.806"     "144.970"     "149.540"    
914 "ALB" "NGDPRPC"      "Albania"     "Gross domestic product per capita, constant prices"                                 "Units"    "126,683.823" "138,483.631" "145,200.807" "153,802.798" "162,964.375"
914 "ALB" "NGDPRPPPPC"   "Albania"     "Gross domestic product per capita, constant prices"                                 "Units"    "5,251.229"   "5,740.349"   "6,018.785"   "6,375.350"   "6,755.111"  
914 "ALB" "NGDPPC"       "Albania"     "Gross domestic product per capita, current prices"                                  "Units"    "162,251.412" "184,123.189" "200,095.796" "222,968.140" "243,696.866"
914 "ALB" "NGDPDPC"      "Albania"     "Gross domestic product per capita, current prices"                                  "Units"    "1,127.640"   "1,283.573"   "1,425.125"   "1,846.119"   "2,373.580"  
914 "ALB" "PPPPC"        "Albania"     "Gross domestic product per capita, current prices"                                  "Units"    "4,178.664"   "4,668.061"   "4,971.922"   "5,364.266"   "5,836.815"  
914 "ALB" "NGAP_NPGDP"   "Albania"     "Output gap in percent of potential GDP"                                             ""         ""            ""            ""            ""            ""           
914 "ALB" "PPPSH"        "Albania"     "Gross domestic product based on purchasing-power-parity (PPP) share of world total" ""         "0.026"       "0.027"       "0.028"       "0.028"       "0.028"      
914 "ALB" "PPPEX"        "Albania"     "Implied PPP conversion rate"                                                        ""         "38.829"      "39.443"      "40.245"      "41.565"      "41.752"     
914 "ALB" "NID_NGDP"     "Albania"     "Total investment"                                                                   ""         "30.796"      "34.989"      "35.264"      "33.897"      "34.197"     
914 "ALB" "NGSD_NGDP"    "Albania"     "Gross national savings"                                                             ""         "23.768"      "28.703"      "25.056"      "26.156"      "28.359"     
914 "ALB" "PCPI"         "Albania"     "Inflation, average consumer prices"                                                 ""         "66.582"      "68.660"      "72.231"      "73.938"      "76.058"     
914 "ALB" "PCPIPCH"      "Albania"     "Inflation, average consumer prices"                                                 ""         "0.039"       "3.120"       "5.202"       "2.363"       "2.867"      
914 "ALB" "PCPIE"        "Albania"     "Inflation, end of period consumer prices"                                           ""         "70.004"      "72.474"      "73.692"      "76.123"      "77.800"     
914 "ALB" "PCPIEPCH"     "Albania"     "Inflation, end of period consumer prices"                                           ""         "4.205"       "3.527"       "1.681"       "3.298"       "2.204"      
914 "ALB" "FLIBOR6"      "Albania"     "Six-month London interbank offered rate (LIBOR)"                                    ""         ""            ""            ""            ""            ""           
914 "ALB" "TM_RPCH"      "Albania"     "Volume of imports of goods and services"                                            ""         "-3.432"      "23.272"      "11.868"      "8.409"       "10.704"     
914 "ALB" "TMG_RPCH"     "Albania"     "Volume of Imports of goods"                                                         ""         "-14.300"     "33.181"      "6.066"       "7.733"       "21.067"     
914 "ALB" "TX_RPCH"      "Albania"     "Volume of exports of goods and services"                                            ""         "16.254"      "23.900"      "3.878"       "9.510"       "20.630"     
914 "ALB" "TXG_RPCH"     "Albania"     "Volume of exports of goods"                                                         ""         "-7.088"      "23.048"      "8.253"       "25.150"      "24.013"     
914 "ALB" "LUR"          "Albania"     "Unemployment rate"                                                                  ""         "16.800"      "16.440"      "15.751"      "15.000"      "14.400"     
914 "ALB" "LE"           "Albania"     "Employment"                                                                         "Millions" ""            ""            ""            ""            ""           
914 "ALB" "LP"           "Albania"     "Population"                                                                         "Millions" "3.089"       "3.060"       "3.051"       "3.040"       "3.027"      
914 "ALB" "GGR"          "Albania"     "General government revenue"                                                         "Billions" "130.642"     "145.639"     "154.595"     "167.224"     "184.355"    
914 "ALB" "GGR_NGDP"     "Albania"     "General government revenue"                                                         ""         "26.066"      "25.848"      "25.323"      "24.674"      "24.992"     
914 "ALB" "GGX"          "Albania"     "General government total expenditure"                                               "Billions" "169.596"     "184.163"     "192.517"     "201.152"     "222.439"    
914 "ALB" "GGX_NGDP"     "Albania"     "General government total expenditure"                                               ""         "33.838"      "32.685"      "31.535"      "29.680"      "30.155"     
914 "ALB" "GGXCNL"       "Albania"     "General government net lending/borrowing"                                           "Billions" "-38.954"     "-38.524"     "-37.922"     "-33.928"     "-38.083"    
914 "ALB" "GGXCNL_NGDP"  "Albania"     "General government net lending/borrowing"                                           ""         "-7.772"      "-6.837"      "-6.212"      "-5.006"      "-5.163"     
914 "ALB" "GGSB"         "Albania"     "General government structural balance"                                              "Billions" ""            ""            ""            ""            ""           
914 "ALB" "GGSB_NPGDP"   "Albania"     "General government structural balance"                                              ""         ""            ""            ""            ""            ""           
914 "ALB" "GGXONLB"      "Albania"     "General government primary net lending/borrowing"                                   "Billions" "-9.381"      "-14.904"     "-13.159"     "-4.105"      "-9.660"     
914 "ALB" "GGXONLB_NGDP" "Albania"     "General government primary net lending/borrowing"                                   ""         "-1.872"      "-2.645"      "-2.155"      "-0.606"      "-1.310"     
914 "ALB" "GGXWDN"       "Albania"     "General government net debt"                                                        "Billions" "n/a"         "n/a"         "n/a"         "n/a"         "n/a"        
914 "ALB" "GGXWDN_NGDP"  "Albania"     "General government net debt"                                                        ""         "n/a"         "n/a"         "n/a"         "n/a"         "n/a"        
914 "ALB" "GGXWDG"       "Albania"     "General government gross debt"                                                      "Billions" "319.608"     "341.522"     "391.308"     "408.300"     "423.962"    
914 "ALB" "GGXWDG_NGDP"  "Albania"     "General government gross debt"                                                      ""         "63.769"      "60.613"      "64.097"      "60.245"      "57.474"     
914 "ALB" "NGDP_FY"      "Albania"     "Gross domestic product corresponding to fiscal year, current prices"                "Billions" "501.199"     "563.449"     "610.494"     "677.738"     "737.656"    
914 "ALB" "BCA"          "Albania"     "Current account balance"                                                            "Billions" "-0.246"      "-0.248"      "-0.442"      "-0.432"      "-0.420"     
914 "ALB" "BCA_NGDPD"    "Albania"     "Current account balance"                                                            ""         "-7.068"      "-6.314"      "-10.170"     "-7.693"      "-5.847"     
612 "DZA" "NGDP_R"       "Algeria"     "Gross domestic product, constant prices"                                            "Billions" "4,103.883"   "4,227.000"   "4,463.712"   "4,785.099"   "4,990.859"  
612 "DZA" "NGDP_RPCH"    "Algeria"     "Gross domestic product, constant prices"                                            ""         "3.800"       "3.000"       "5.600"       "7.200"       "4.300"      
612 "DZA" "NGDP"         "Algeria"     "Gross domestic product, current prices"                                             "Billions" "4,123.500"   "4,227.100"   "4,522.800"   "5,252.300"   "6,149.100"  
612 "DZA" "NGDPD"        "Algeria"     "Gross domestic product, current prices"                                             "Billions" "54.749"      "54.745"      "56.761"      "67.864"      "85.327"     
612 "DZA" "PPPGDP"       "Algeria"     "Gross domestic product, current prices"                                             "Billions" "253.664"     "267.004"     "286.417"     "312.741"     "334.970"    
612 "DZA" "NGDP_D"       "Algeria"     "Gross domestic product, deflator"                                                   ""         "100.478"     "100.002"     "101.324"     "109.764"     "123.207"    
612 "DZA" "NGDPRPC"      "Algeria"     "Gross domestic product per capita, constant prices"                                 "Units"    "134,527.093" "136,557.472" "142,093.080" "150,073.679" "154,200.659"
612 "DZA" "NGDPRPPPPC"   "Algeria"     "Gross domestic product per capita, constant prices"                                 "Units"    "10,449.539"  "10,607.251"  "11,037.235"  "11,657.137"  "11,977.705" 
612 "DZA" "NGDPPC"       "Algeria"     "Gross domestic product per capita, current prices"                                  "Units"    "135,170.130" "136,560.703" "143,974.024" "164,726.360" "189,986.405"
612 "DZA" "NGDPDPC"      "Algeria"     "Gross domestic product per capita, current prices"                                  "Units"    "1,794.695"   "1,768.577"   "1,806.860"   "2,128.385"   "2,636.309"  
end
    Tags: None
  • #2
    You cannot turn the subject descriptors into variable names because 1) they contain characters like blanks and commas that are illegal in variable names, and 2) they are much too long. Variable names are restricted to 32 characters. There are ways to work around 1) and get a reasonable approximation, but in your case the names are so long that truncating them to 32 characters would do too much violence to the content. So I think the best you can do here is create a new series of variables and label the variable names with the values of SubjectDescriptor.

    There is also a more fundamental obstacle here. You have multiple observations for the same values of country and SubjectDescriptor: within these there is more refined variation based on the variable WEOSubjectCode. (And every distinct value of WEOSubjectCode seems to have the same associated SubjectDescriptor value.) On top of that, as it happens, at least in your example data, the WEOSubjectCode values are all legal variable names! So I think that perhaps you should switch your goal and do the reshaping so that the WEOSubjectCode becomes the variable name.

    Code:
    reshape long y, i(WEOSubjectCode country) j(Year)

drop SubjectDescriptor Scale

reshape wide y, i(country Year) j(WEOSubjectCode) string
rename y* *

    Comment

    • #3
      Thank you so much for the code and tips Clyde Schechter . The code does the job; I just need to rename the variables. Thank you again.

      Comment

      • #4
        See also https://www.stata-journal.com/sjpdf....iclenum=dm0031

        SJ-7-2 dm0031 . . . . . . . . . Stata tip 45: Getting those data into shape
        . . . . . . . . . . . . . . . . . . . . . . . C. F. Baum and N. J. Cox
        Q2/07 SJ 7(2):268--271 (no commands)
        tip on applications of reshape and solving data
        management problems

        Comment

        • #5
          Thanks Nick Cox ! Going to have a look.

          Comment

          Previous Next
          Working...
          X