Reshaping a dataset, Generating a regional GDP growth variable for two regions and drawing its time evolution

[Aziz Essouaied]

Hello Stata people;

I'm using Stata version 13.1, I'm working with this data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 time_period int time float observationvalue
"Austria"     2016 107.685
"Austria"     2017 110.132
"Austria"     2018 112.868
"Austria"     2019 114.848
"Austria"     2020 107.592
"Austria"     2021 112.889
"Austria"     2022 118.907
"Austria"     2023 117.972
"Austria"     2024 117.194
"Germany"     2016 111.129
"Germany"     2017 114.238
"Germany"     2018 115.535
"Germany"     2019 116.665
"Germany"     2020 111.844
"Germany"     2021 116.217
"Germany"     2022  118.32
"Germany"     2023 117.291
"Germany"     2024  116.71
"Greece"      2016  81.166
"Greece"      2017  82.362
"Greece"      2018  84.062
"Greece"      2019  85.976
"Greece"      2020   78.07
"Greece"      2021  84.826
"Greece"      2022  89.698
"Greece"      2023   91.79
"Greece"      2024  93.875
"Spain"       2016 103.434
"Spain"       2017  106.43
"Spain"       2018 108.979
"Spain"       2019 111.116
"Spain"       2020   98.96
"Spain"       2021 105.574
"Spain"       2022 112.299
"Spain"       2023 115.063
"Spain"       2024 119.038
"France"      2016 106.483
"France"      2017 108.701
"France"      2018  110.49
"France"      2019  112.73
"France"      2020 104.343
"France"      2021 111.524
"France"      2022 114.554
"France"      2023 116.202
"France"      2024 117.586
"Italy"       2016  97.816
"Italy"       2017  99.385
"Italy"       2018 100.207
"Italy"       2019 100.637
"Italy"       2020  91.712
"Italy"       2021  99.903
"Italy"       2022 104.719
"Italy"       2023 105.741
"Italy"       2024 106.474
"Netherlands" 2016 107.078
"Netherlands" 2017 110.056
"Netherlands" 2018 112.542
"Netherlands" 2019 115.131
"Netherlands" 2020 110.677
"Netherlands" 2021 117.624
"Netherlands" 2022 123.514
"Netherlands" 2023 122.775
"Netherlands" 2024 124.103
"Portugal"    2016   97.48
"Portugal"    2017 100.711
"Portugal"    2018 103.678
"Portugal"    2019 106.525
"Portugal"    2020  97.785
"Portugal"    2021 103.221
"Portugal"    2022 110.432
"Portugal"    2023 113.855
"Portugal"    2024 116.291
end

As you can see, it's a data about the GDP growth from 2016 to 2025 for some European countries. The idea behind my work is to study the GDP evolution of the North Countries vs. the South Countries. the North countries are: Germany, Austria, France and the Netherlands, the South contries are: Spain, Greece, Portugal and Italy.

Is there a way to:
- Already reshape my dataset for a better lecture
- Define those two regions and calculating the mean of the GDP growth for each region's countries (calculating the sum of the growth of each four countries, and then deviding that by 4)
- Drawing a time graph showing the GDP evolution of the North vs. the South regions by year.

I would appreciate the help.

Thanks!

[Carlo Lazzaro]

Aziz:
https://www.statalist.org/forums/help#adviceextras #4?

[Aziz Essouaied]

Carlo Lazzaro Hello;

It's not actually a homework, it's more of a hobby for me to study some economics or geopolitics, so, that work is not for a school.

[Carlo Lazzaro]

Aziz:
thanks for clarifying.
I would go:

. g geo_indicator=0 if inlist(time_period,"Germany", "Austria", "France" "the Netherlands")

. replace geo_indicator=1 if inlist(time_period,"Spain", "Greece", "Portugal" "Italy")


. label define geo_indicator 0 "North_Europe" 1 "South_Europe"

. label val geo_indicator geo_indicator
. encode time_period, generate(Countries)
. reg observationvalue i.Countries i.time

      Source |       SS           df       MS      Number of obs   =        72
-------------+----------------------------------   F(15, 56)       =    131.54
       Model |  7850.56942        15  523.371295   Prob > F        =    0.0000
    Residual |  222.811892        56  3.97878379   R-squared       =    0.9724
-------------+----------------------------------   Adj R-squared   =    0.9650
       Total |  8073.38132        71  113.709596   Root MSE        =    1.9947

------------------------------------------------------------------------------
observatio~e | Coefficient  Std. err.      t    P>|t|     [95% conf. interval]
-------------+----------------------------------------------------------------
   Countries |
     France  |  -1.941555   .9403054    -2.06   0.044    -3.825213   -.0578966
    Germany  |   1.984667   .9403054     2.11   0.039     .1010095    3.868325
     Greece  |  -27.58467   .9403054   -29.34   0.000    -29.46833   -25.70101
      Italy  |  -12.61033   .9403054   -13.41   0.000    -14.49399   -10.72667
Netherlands  |   2.601445   .9403054     2.77   0.008     .7177867    4.485103
   Portugal  |  -7.789887   .9403054    -8.28   0.000    -9.673545   -5.906229
      Spain  |  -4.354889   .9403054    -4.63   0.000    -6.238547   -2.471231
             |
        time |
       2017  |   2.467999   .9973444     2.47   0.016     .4700785     4.46592
       2018  |   4.511249   .9973444     4.52   0.000     2.513328     6.50917
       2019  |   6.419624   .9973444     6.44   0.000     4.421703    8.417545
       2020  |  -1.410999   .9973444    -1.41   0.163     -3.40892    .5869217
       2021  |   4.938375   .9973444     4.95   0.000     2.940454    6.936296
       2022  |    10.0215   .9973444    10.05   0.000     8.023579    12.01942
       2023  |   11.05225   .9973444    11.08   0.000      9.05433    13.05017
       2024  |     12.375   .9973444    12.41   0.000     10.37708    14.37292
             |
       _cons |   107.7458   .9403054   114.59   0.000     105.8621    109.6294
------------------------------------------------------------------------------

. twoway (scatter observationvalue time, sort), by( Countries )

.

[Aziz Essouaied]

Carlo Lazzaro Thanks for the help.

I might have been a bit hard to understand, so let me explain mo goal please. What I want to have is the GDP of the South Countries (having the mean of the GDPs of the 4 South countries) compared to the GDP of the North Countries (having the mean of the GDPs of the 4 North countries), so, a variable should be created to calculated that already for each year, and then one can draw a twoway line. In other words, I want to regroup the GDPs of the North countries in just one variable, and the same thing for the South countries.

[Carlo Lazzaro]

Aziz:
you may want to try:

. reg observationvalue i.time##i.geo_indicator

      Source |       SS           df       MS      Number of obs   =        54
-------------+----------------------------------   F(17, 36)       =      2.54
       Model |  3544.33492        17  208.490289   Prob > F        =    0.0093
    Residual |   2959.5477        36  82.2096582   R-squared       =    0.5450
-------------+----------------------------------   Adj R-squared   =    0.3301
       Total |  6503.88262        53  122.714766   Root MSE        =     9.067

------------------------------------------------------------------------------------
  observationvalue | Coefficient  Std. err.      t    P>|t|     [95% conf. interval]
-------------------+----------------------------------------------------------------
              time |
             2017  |   2.591334   7.403137     0.35   0.728    -12.42292    17.60559
             2018  |      4.532   7.403137     0.61   0.544    -10.48226    19.54626
             2019  |   6.315336   7.403137     0.85   0.399    -8.698922    21.32959
             2020  |  -.5059967   7.403137    -0.07   0.946    -15.52025    14.50826
             2021  |   5.111003   7.403137     0.69   0.494    -9.903255    20.12526
             2022  |      8.828   7.403137     1.19   0.241    -6.186258    23.84226
             2023  |   8.722669   7.403137     1.18   0.246    -6.291589    23.73693
             2024  |      8.731   7.403137     1.18   0.246    -6.283258    23.74526
                   |
     geo_indicator |
     South_Europe  |  -14.40567   7.403137    -1.95   0.060    -29.41992    .6085928
                   |
time#geo_indicator |
2017#South_Europe  |  -.1170019   10.46962    -0.01   0.991    -21.35037    21.11637
2018#South_Europe  |   .3476639   10.46962     0.03   0.974     -20.8857    21.58103
2019#South_Europe  |   .8636627   10.46962     0.08   0.935     -20.3697    22.09703
2020#South_Europe  |   -1.91567   10.46962    -0.18   0.856    -23.14904     19.3177
2021#South_Europe  |  -1.264005   10.46962    -0.12   0.905    -22.49737    19.96936
2022#South_Europe  |   1.288333   10.46962     0.12   0.903    -19.94503     22.5217
2023#South_Europe  |   4.153333   10.46962     0.40   0.694    -17.08003     25.3867
2024#South_Europe  |      6.977   10.46962     0.67   0.509    -14.25637    28.21037
                   |
             _cons |   108.4323   5.234808    20.71   0.000     97.81565     119.049
------------------------------------------------------------------------------------

. margins i.time#i.geo_indicator

Adjusted predictions                                        Number of obs = 54
Model VCE: OLS

Expression: Linear prediction, predict()

------------------------------------------------------------------------------------
                   |            Delta-method
                   |     Margin   std. err.      t    P>|t|     [95% conf. interval]
-------------------+----------------------------------------------------------------
time#geo_indicator |
2016#North_Europe  |   108.4323   5.234808    20.71   0.000     97.81565     119.049
2016#South_Europe  |   94.02667   5.234808    17.96   0.000     83.40998    104.6434
2017#North_Europe  |   111.0237   5.234808    21.21   0.000      100.407    121.6403
2017#South_Europe  |     96.501   5.234808    18.43   0.000     85.88432    107.1177
2018#North_Europe  |   112.9643   5.234808    21.58   0.000     102.3476     123.581
2018#South_Europe  |   98.90633   5.234808    18.89   0.000     88.28965     109.523
2019#North_Europe  |   114.7477   5.234808    21.92   0.000      104.131    125.3644
2019#South_Europe  |   101.2057   5.234808    19.33   0.000     90.58898    111.8223
2020#North_Europe  |   107.9263   5.234808    20.62   0.000     97.30965     118.543
2020#South_Europe  |     91.605   5.234808    17.50   0.000     80.98832    102.2217
2021#North_Europe  |   113.5433   5.234808    21.69   0.000     102.9267      124.16
2021#South_Europe  |   97.87366   5.234808    18.70   0.000     87.25698    108.4903
2022#North_Europe  |   117.2603   5.234808    22.40   0.000     106.6436     127.877
2022#South_Europe  |    104.143   5.234808    19.89   0.000     93.52632    114.7597
2023#North_Europe  |    117.155   5.234808    22.38   0.000     106.5383    127.7717
2023#South_Europe  |   106.9027   5.234808    20.42   0.000     96.28599    117.5194
2024#North_Europe  |   117.1633   5.234808    22.38   0.000     106.5466      127.78
2024#South_Europe  |   109.7347   5.234808    20.96   0.000     99.11798    120.3514
------------------------------------------------------------------------------------

. marginsplot