Announcement

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

    Convert country x year table to three-variable dataset

    Hi there,

    I've got a dataset for FDI from UNCTAD (link attached) which is formatted as a table with year on columns and countrys on rows. I would like to convert this to a dataset formatted as three variables -COUNTRY YEAR FDI-. Is there a way to do this easily?

    Thanks!


    To give examples,
    The original is
    Code:
        1970 1971 1972
AFG  X1   X2   X3
ALB  X1   X2   X3
ALG  X1   X2   X3
    and I would like it to be formatted as

    Code:
    COUNTRY YEAR FDI
AFG     1970  X1
AFG     1971  X2
AFG     1972  X3
ALB     1970  X1
    Beyond 20/20 WDS - Table view
    https://unctadstat.unctad.org
    Tags: None
  • #2
    Additionally, I want to merge this dataset with another one (formatted correctly).
    The country names have slight differences, e.g.,"Bolivia (Plurinational State of)" vs. "Bolivia" - of course both encoded as string variables. The dataset I am merging the above one into has numerical country codes. Is there a way to get Stata to recognise the string names as the same country, or will I have to manually encode the dataset without the country codes?
    Thanks again!

    Comment

    • #3
      If you repost your data using dataex, I'll post back to you the code for the reshape command which is the command of interest here.

      Comment

      • #4
        Thanks!
        This is a different dataset, though formatted identically to the other I was talking about, but with multiple data series under the variable 'seriesname'. I was hoping it would be possible to do sort each of them out into GDP / GDP Growth / Inflation / Government expenditure / Population also. If not, then I can drop all other series sequentially and do the code five times.

        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input str3 countrycode str68 seriesname double(yr1979 yr1980 yr1981 yr1982 yr1983 yr1984)
"AFG" "GDP growth (annual %)"                                                                .                .                 .                  .                 .                 .
"AFG" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"AFG" "GDP (constant 2015 US$)"                                                              .                .                 .                  .                 .                 .
"AFG" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"AFG" "Population, total"                                                             13411060         13356500          13171679           12882518          12537732          12204306
"ALB" "GDP growth (annual %)"                                                                .                .  5.74563529200671   2.94859680156867  1.10493826182712 -1.25159664469854
"ALB" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"ALB" "GDP (constant 2015 US$)"                                                              . 4650625160.56601  4917833121.09044   5062840191.20539  5118781449.61318  5054714952.74037
"ALB" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"ALB" "Population, total"                                                              2617832          2671997           2726056            2784278           2843960           2904429
"DZA" "GDP growth (annual %)"                                                 7.47782655012782 .790606972981195  2.99999608742769    6.4000041158006  5.40000302352202  5.59999652973147
"DZA" "Inflation, consumer prices (annual %)"                                 11.3486005086091 9.51782449754965  14.6548426360635   6.54250963007668  5.96716393032723  8.11639795517099
"DZA" "GDP (constant 2015 US$)"                                               60768171201.5106 61248608600.3829  63086064461.9983   67123575184.0628  70748250273.4983  74710149833.6599
"DZA" "General government final consumption expenditure (constant 2015 US$)"  11905313778.1133 11594739803.8864  12394777074.7661   13088884007.6541  13782594889.3901   14595768435.784
"DZA" "Population, total"                                                             18647801         19221659          19824297           20452901          21101875          21763578
"AND" "GDP growth (annual %)"                                                .0415571951294567 2.20872760693067 -.132474511474285   1.24646111342557   1.7701183169326  1.78468669356731
"AND" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"AND" "GDP (constant 2015 US$)"                                               1200156310.43362 1226664494.18849  1225039476.39239   1240309117.08973  1262264055.95792  1284791514.60228
"AND" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"AND" "Population, total"                                                                34825            36063             37498              39115             40854             42706
"AGO" "GDP growth (annual %)"                                                                .                . -4.40000121936168                  0  4.20000142834162    6.000002164373
"AGO" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"AGO" "GDP (constant 2015 US$)"                                                              . 29612271580.7811   28309331270.146    28309331270.146  29498323587.8461  31268223641.5706
"AGO" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"AGO" "Population, total"                                                              8058112          8341290           8640478            8952971           9278104           9614756
"ATG" "GDP growth (annual %)"                                                 8.07974533665914 8.16143079697966  3.81491616554098 -.0840122638518181  5.36401627271424  10.1649955948421
"ATG" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"ATG" "GDP (constant 2015 US$)"                                               390652456.233093 422535286.105259  438654653.040003   438286129.335493  461795868.634098  508737398.337917
"ATG" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"ATG" "Population, total"                                                                61948            61861             61789              61780             61779             61784
"ARG" "GDP growth (annual %)"                                                 10.2227634648727 1.51878394940456 -5.18978914005386  -.735659161627296  4.34909327957422  1.57073870223007
"ARG" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"ARG" "GDP (constant 2015 US$)"                                               283535234200.774 287841521828.722  272903153788.289   270895516735.076  282677015448.069   287117132732.02
"ARG" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"ARG" "Population, total"                                                             27471046         27896532          28338514           28794550          29262049          29737097
"ARM" "GDP growth (annual %)"                                                                .                .                 .                  .                 .                 .
"ARM" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"ARM" "GDP (constant 2015 US$)"                                                              .                .                 .                  .                 .                 .
"ARM" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"ARM" "Population, total"                                                              3049107          3099759           3148096            3193696           3238592           3285593
"AUS" "GDP growth (annual %)"                                                 4.05078058108339 3.03539927689404  3.33880733349639   3.32286786134055 -2.22196943253216  4.59592426080013
"AUS" "Inflation, consumer prices (annual %)"                                 9.12200684150512 10.1358411703239  9.48766603415561   11.3518197573657  10.0389105058366  3.96039603960395
"AUS" "GDP (constant 2015 US$)"                                               439053871064.253 452380909091.713  467485036059.805   483018946079.612  472286412744.384  493992338568.166
"AUS" "General government final consumption expenditure (constant 2015 US$)"   81901732950.821 83750692198.7634  87555651650.6934   88871716354.8723  91131897692.9444  95378882387.8227
"AUS" "Population, total"                                                             14514000         14692000          14927000           15178000          15369000          15544000
"AUT" "GDP growth (annual %)"                                                 5.35669990025116 1.73148559549917  -.14429469056661   2.01125470754351  2.97308803031213 .0512747629668411
"AUT" "Inflation, consumer prices (annual %)"                                 3.70740096353416 6.32832536226099  6.80304177599256   5.43603082512853  3.33916453409529  5.66318573909355
"AUT" "GDP (constant 2015 US$)"                                               189311747857.845 192589653502.591  192311756858.006   196179636120.972  202012229400.395  202115810692.184
"AUT" "General government final consumption expenditure (constant 2015 US$)"  40597563554.8147 41409891753.3463  42192587905.6869   43561486849.2001  44360824548.5538  44637857230.1182
"AUT" "Population, total"                                                              7549425          7549433           7568710            7574140           7561910           7561434
"AZE" "GDP growth (annual %)"                                                                .                .                 .                  .                 .                 .
"AZE" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"AZE" "GDP (constant 2015 US$)"                                                              .                .                 .                  .                 .                 .
"AZE" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"AZE" "Population, total"                                                              6053635          6150735           6249312            6349555           6452067           6557581
"BHS" "GDP growth (annual %)"                                                 26.1392957694239 6.49528192139537 -9.22640507988643   6.69889775608125  3.59705186796602  14.1711486244798
"BHS" "Inflation, consumer prices (annual %)"                                 9.09139326792412 12.0978372310682  11.1144984973891   6.01262101667497  3.99999999999995  3.96634615384614
"BHS" "GDP (constant 2015 US$)"                                               5984377100.94707 6373079264.89301  5785073155.85173   6172609291.67674  6394641249.50525  7300835364.97493
"BHS" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"BHS" "Population, total"                                                               205991           210589            215321             220182            225095            229913
"BHR" "GDP growth (annual %)"                                                                .                . -5.31669735282965  -7.55621771215945  6.37637679964396  5.00364604500776
"BHR" "Inflation, consumer prices (annual %)"                                 2.20599195116765 3.86952505941505  11.3445968083975   8.88571308471458  2.97239915075073  .322399250235335
"BHR" "GDP (constant 2015 US$)"                                                              .  7719989594.2972  7309541111.89847   6757216271.72362  7188081842.37558  7547748015.19352
"BHR" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"BHR" "Population, total"                                                               342804           359897            374120             385953            396451            407233
"BGD" "GDP growth (annual %)"                                                 4.80163460055662  .81914186889891  7.23394369490723   2.13432783577075  3.88104639981715  4.80331001525438
"BGD" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"BGD" "GDP (constant 2015 US$)"                                               35364513430.9367 35654198967.1819   38233403645.338   39049429821.9031  40564956312.1552  42513416921.3805
"BGD" "General government final consumption expenditure (constant 2015 US$)"  1852893814.61285 1799928144.18785  2112166772.52596   2118720667.47758  2171337748.67028  2252215595.86369
"BGD" "Population, total"                                                             77529040         79639498          81767516           83932132          86142490          88416529
"BRB" "GDP growth (annual %)"                                                 7.89300813555889  4.3730648140921 -1.89975157094841  -4.90093847758082   .50125313283209  3.60037406483791
"BRB" "Inflation, consumer prices (annual %)"                                 13.1698355431799 14.4305197203942  14.5675632146886   10.3324664370561   5.2302987828884  4.68579731185599
"BRB" "GDP (constant 2015 US$)"                                               3420704181.24354 3570293792.18768  3502467079.78312   3330813323.00542  3347509129.13578  3468031979.63926
"BRB" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"BRB" "Population, total"                                                               251347           252388            253296             254078            254791            255493
"BLR" "GDP growth (annual %)"                                                                .                .                 .                  .                 .                 .
"BLR" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"BLR" "GDP (constant 2015 US$)"                                                              .                .                 .                  .                 .                 .
"BLR" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"BLR" "Population, total"                                                              9584000          9643000           9710000            9776000           9843000           9910000
"BEL" "GDP growth (annual %)"                                                 2.34107295556814 4.44405387639348 -.279283279362133   .594986772001008  .311842702585196  2.46638280528001
"BEL" "Inflation, consumer prices (annual %)"                                 4.46908394900489 6.64868829655357  7.62748656814273   8.72654906555468  7.66160983743879  6.34177491009128
"BEL" "GDP (constant 2015 US$)"                                               230173687424.147 240402730102.557  239731325474.251   241157695149.165   241909727823.21  247876147754.542
"BEL" "General government final consumption expenditure (constant 2015 US$)"  66308108213.2983 67592230092.2539  69712078151.5697   69239868571.1038  69673010165.2261  69829281001.5338
"BEL" "Population, total"                                                              9848382          9859242           9858982            9856303           9855520           9855372
"BLZ" "GDP growth (annual %)"                                                 7.51702151348019 13.4366817635662  1.13377082280894  -.107710428562413 -2.14317996954114   1.9720650253355
"BLZ" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"BLZ" "GDP (constant 2015 US$)"                                               319256106.447618 362153533.481736  366259524.578124    365865024.87455  358023878.945882  365084342.644923
"BLZ" "General government final consumption expenditure (constant 2015 US$)"                 .  85645366.636121   84048585.794405   82910697.6821767  81053774.1372875  82880305.5372544
"BLZ" "Population, total"                                                               141302           144142            147572             151486            155820            160341
"BEN" "GDP growth (annual %)"                                                 6.53574740878149 6.78176387161335   9.9542311666321   2.23506634454745 -4.34782273446388  7.92983956425326
"BEN" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"BEN" "GDP (constant 2015 US$)"                                               2696279536.50696 2879134847.99149  3165730586.35962   3236486765.25439  3095770057.87675  3341259656.74457
"BEN" "General government final consumption expenditure (constant 2015 US$)"  212207864.318279 249324603.164367   272000517.83585    210165927.20744  280221242.308155  284711918.856261
"BEN" "Population, total"                                                              3618519          3717161           3820126            3927717           4039940           4156819
"BMU" "GDP growth (annual %)"                                                 2.14797136038143 7.94392523364449  2.38095238095133  -5.28541226215653  2.00892857142844  .481400437638399
"BMU" "Inflation, consumer prices (annual %)"                                                .                .                 .                  .                 .                 .
"BMU" "GDP (constant 2015 US$)"                                               4215804962.84216 4550705357.08662  4659055484.63625   4412805194.75061  4501455299.10943  4523125324.61944
"BMU" "General government final consumption expenditure (constant 2015 US$)"                 .                .                 .                  .                 .                 .
"BMU" "Population, total"                                                                53800            54670             55050              55449             55930             56423
end

        Comment

        • #5
          Code:
          greshape long yr, i(id seriesname) j(no)
          Note that I use greshape, an SSC command which in large datasets is WAY faster than standard reshape, but there are also others such as tolong and others I might not remember at the moment.

          Comment

          Previous Next
          Working...
          X