Weighted average for panel groups and subperiods

[Giorgio Di Stefano]

Dear all,


I have an unbalanced panel, which contains group, where I have also generated some custom data for the entire period and sub-periods, expressed in decades. I am stuck into two things.

1. I have tried to generate, as you will see in my data and code below, gaps of Διndicator1, defined as ιndicator1- indmax, and the gaps of Δggdp defined as ggdp- gdpmax , and then the ratio of (ιndicator1- indmax)/ (ggdp- gdpmax), for the entire period and for each group , and then for decades and for each group in each decade. Then putting some weight on the difference Δ, Δindocator1, Δgdp I need to get (collapse) to the weighted average for each entire period and froups , and then for each decade and for each group.

How is this done to get the wieghted average of the periods?

1. In my model I use margins and obtain coefficients of 1-a. I need to obtain the same coefficients for the regression for the entire period and groups and by decades and by groups in the subperiods.

How am I supposed to do that?

xtset id
tab groups, g(gropus)
gen decade= 10*floor(ts/10)
tab decade, g(decade)

foreach var of varlist decade?{
xtset id ts
xtregar ggdp l.gdp cpi u Output dummy1 dummy2 c.indicator1##c.indicator1 if `var', fe
// get the x where the minimum occurs
local x = -_b[indicator1]/(2*_b[indicator1#indicator1])
// get the y for that minimim
qui margins, at(indicator1 = `x' cpi=0 u=2 Output= 7.54e+16 dummy1=0 dummy2=0 indicator1=-4)
// the predictions are stored in the matrix r(b)
// el(r(b),1,1) extracts the cell 1,1 from the matrix r(b)
local y = el(r(b),1,1)
gen indmax_`var'= `x'
gen ggdpmax_`var'=`y'
gen indgaps_`var'=indocator1-indmax_`var'
gen gdpgaps_`var'=gdpmax-ggdpmax_`var'
gen ratio_`var'=indgaps_`var'/gdpgaps_`var'
// prepare for our plot
nlcom -_b[indicator1]/(2*_b[indicator1#indicator1])
qui margins, at(indicator1=(-16(4)29) cpi=0 u=2 Output= 7.54e+16 dummy1=0 dummy2=0 indicator1=-4)
// with scatteri we can add a point and a label to our graph
marginsplot, noci plotopts(msymbol(i)) legend(off) ///
addplot(scatteri `y' `x' (12) "(`xlab'; `ylab')") ///
ylab(,format(%9.3fc) angle(0)) ytitle("predicted gdp ") title("Predicted Marings pair")
}

[QUOTE]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id ts) str1 group str97 country float(ggdp dummy1) byte(dummy2 length) float(indicator1 indicator2) long gdp float(cpi u) double Output byte count
1 1990 "A" "Australia"           . 1 1 3  10.999176  10.999176   571564  7.333022  6.93 1.28877e+16 26
1 1991 "A" "Australia"       -5720 0 1 2       12.2       12.2   565844  3.176675  9.58  1.3296e+15 27
1 1992 "A" "Australia"       14434 0 1 2       12.2       12.2   580278 1.0122311 10.73 1.22544e+16 27
1 1993 "A" "Australia"       22666 1 1 3  17.553352  17.553352   602944 1.7536534 10.87 1.18076e+16 28
1 1994 "A" "Australia"       29533 0 1 3      19.11      19.11   632477 1.9696348  9.72 1.34421e+16 28
1 2000 "A" "Australia"           . 0 1 3     28.634     28.634   798334  4.457435  6.28 1.39202e+16 30
1 2001 "A" "Australia"       20810 1 1 3   26.41307   26.54496   819144 4.4071355  6.74 1.30234e+16 31
1 2002 "A" "Australia"       33904 0 1 3   5.536341      6.908   853048 2.9815745  6.37 1.61436e+16 31
1 2003 "A" "Australia"       23667 0 1 3   5.536341      6.908   876715  2.732596  5.93 2.02622e+16 31
1 2004 "A" "Australia"       36212 1 1 3   7.350179   8.569935   912927 2.3432553  5.39 2.35621e+16 32
1 2010 "A" "Australia"           . 1 1 3   6.241802   6.241802  1080050   2.91834  5.21 3.47466e+16 35
1 2011 "A" "Australia"       30676 0 0 3      5.068      5.068  1110726   3.30385  5.08  4.0027e+15 35
1 2012 "A" "Australia"       42237 0 0 3      5.068      5.068  1152963 1.7627802  5.22 4.13369e+16 35
1 2013 "A" "Australia"       24591 1 1 3   7.929047   8.248285  1177554  2.449889  5.66 3.74135e+16 37
1 2014 "A" "Australia"       30301 0 . .  15.081665     16.199  1207855  2.487923  6.08 3.47753e+16 37
2 1990 "A" "France"              . 0 0 3        1.6        1.6  2081911 3.1942835  9.36 5.03813e+16 51
2 1991 "A" "France"          21822 0 0 3        1.6        1.6  2103733  3.213407  9.13 4.93537e+16 52
2 1992 "A" "France"          33646 0 1 1        1.6        1.6  2137379 2.3637605 10.21  5.2038e+15 53
2 1993 "A" "France"         -13437 1 1 2   6.996447  -.8161401  2123942 2.1044629 11.32 5.80229e+16 54
2 1994 "A" "France"          50090 0 0 2    8.69136     -1.575  2174032 1.6555153 12.59   5.071e+14 54
2 2000 "A" "France"              . 0 0 5   2.364474      2.797  2564959   1.67596 10.22 7.85938e+16 57
2 2001 "A" "France"          50881 0 0 5   2.364474      2.797  2615840 1.6347808  8.61 7.85391e+16 57
2 2002 "A" "France"          29704 1 1 5  -.7668521  -.7921649  2645544 1.9234123   8.7 8.75676e+16 59
2 2003 "A" "France"          21777 0 0 2 -2.9592714     -3.286  2667321  2.098472  8.31 1.03639e+17 59
2 2004 "A" "France"          75479 0 0 2  -2.950783     -3.286  2742800 2.1420896  8.91  1.0654e+16 60
2 2010 "A" "France"              . 0 0 3      3.891      3.891  2904699 1.5311227  8.87 9.63052e+16 64
2 2011 "A" "France"          63691 0 0 1      3.891      3.891  2968390  2.111598  8.81 1.08307e+17 65
2 2012 "A" "France"           9295 1 1 2 -2.5410414 -2.1623123  2977685 1.9541953   9.4 9.84001e+16 67
2 2013 "A" "France"          17161 0 0 2  -6.333579     -5.608  2994846  .8637155  9.92 9.87864e+16 67
2 2014 "A" "France"          28637 0 0 2  -6.108374     -5.608  3023483  .5077588 10.29 1.04153e+17 68
3 1990 "B" "Germany"             . 0 1 3   4.255927      4.029  3090684 2.6964715  4.89           . 24
3 1991 "B" "Germany"        154874 0 1 3   .5641079 -1.6926868  3245558 4.0470366  5.32 1.21946e+17 25
3 1992 "B" "Germany"         62415 0 1 3   .3835026     -1.973  3307973  5.056979  6.32 1.29671e+17 25
3 1993 "B" "Germany"        -32314 0 1 3   .3835026     -1.973  3275659  4.474575  7.68 1.14872e+17 25
3 1994 "B" "Germany"         78350 1 1 4   .8169867 -1.2526814  3354009  2.693057  8.73 1.21188e+17 26
3 2000 "B" "Germany"             . 0 1 4  1.9049623      2.806  3738235  1.440268  7.92 1.17777e+17 27
3 2001 "B" "Germany"         62857 0 1 4  1.9049622      2.806  3801092  1.983857  7.77 1.17577e+17 27
3 2002 "B" "Germany"         -7525 1 1 4   1.283737  2.0394616  3793567 1.4208056  8.48 1.22359e+17 28
3 2003 "B" "Germany"        -26559 0 1 3 -1.3254085      -1.18  3767008 1.0342277  9.78 1.50381e+17 28
3 2004 "B" "Germany"         44265 0 1 3 -1.3254085      -1.18  3811273 1.6657335 10.73 1.75373e+17 28
3 2010 "B" "Germany"             . 0 1 4  1.1606808       -.05  4071113 1.1038091  6.97  2.0819e+16 30
3 2011 "B" "Germany"        159799 0 1 4  1.1606808       -.05  4230912 2.0751746  5.82 2.44939e+17 30
3 2012 "B" "Germany"         17706 0 1 4  1.1606808       -.05  4248618  2.008491  5.38 2.25506e+17 30
3 2013 "B" "Germany"         18592 1 1 4  1.1165322      .0625  4267210  1.504721  5.23 2.32201e+17 31
3 2014 "B" "Germany"         94286 0 . .  .01281774      2.875  4361496  .9067979  4.98 2.37452e+17 31
4 1990 "B" "Italy"               . 0 0 2   3.829138       3.66  2199474  6.456609  9.79 4.78032e+16 49
4 1991 "B" "Italy"           33838 0 1 2   3.777037       3.66  2233312      6.25  10.1 4.77836e+16 50
4 1992 "B" "Italy"           18632 1 1 1   2.615249  2.9572604  2251944   5.27059  9.33 5.49646e+16 51
4 1993 "B" "Italy"          -19205 0 1 1  1.4437795   .7736538  2232739 4.6267347 10.24 4.39227e+16 52
4 1994 "B" "Italy"           48027 1 1 1  4.0116615  13.063806  2280766  4.051842 11.09 5.03757e+16 53
4 2000 "B" "Italy"               . 0 1 1    5.97681   1.812674  2598506 2.5376854 10.84 7.22295e+16 58
4 2001 "B" "Italy"           50706 1 1 4   7.402936  4.7593465  2649212  2.785165   9.6 7.13962e+16 59
4 2002 "B" "Italy"            6728 0 0 4      8.534      8.534  2655940  2.465323  9.21 7.52609e+16 59
4 2003 "B" "Italy"            3682 0 0 4      8.534      8.534  2659622 2.6725554  8.87 8.79973e+16 59
4 2004 "B" "Italy"           37862 0 0 4      8.534      8.534  2697484 2.2067366  7.87 1.03212e+17 59
4 2010 "B" "Italy"               . 0 0 3     -16.25          0  2680599  1.525516  8.36  1.1136e+16 62
4 2011 "B" "Italy"           18960 0 0 3 -14.330358          0  2699559  2.780633  8.36 1.24328e+17 63
4 2012 "B" "Italy"          -80471 0 1 1          0          0  2619088  3.041363 10.65 1.11628e+17 64
4 2013 "B" "Italy"          -48219 1 1 1   .6806767 -1.9956785  2570869 1.2199935 12.15 1.13354e+17 67
4 2014 "B" "Italy"            -117 0 0 1 -1.9673892     -2.941  2570752 .24104743 12.68 1.12803e+17 68
5 1990 "C" "United Kingdom"      . 0 1 3     16.809     16.809  1846210  8.063461  6.97 5.76584e+16 18
5 1991 "C" "United Kingdom" -20366 0 1 2     16.809     16.809  1825844  7.461783  8.55 5.51486e+16 18
5 1992 "C" "United Kingdom"   7323 1 1 5  13.403038  13.403038  1833167 4.5915494  9.78 5.94441e+16 19
5 1993 "C" "United Kingdom"  45655 0 1 5       12.1       12.1  1878822  2.558578 10.35 5.35544e+16 19
5 1994 "C" "United Kingdom"  72260 0 1 5       12.1       12.1  1951082 2.2190125  9.65 5.90669e+16 19
5 2000 "C" "United Kingdom"      . 0 1 4      1.806      1.806  2386524 1.1829562  5.56 6.64745e+16 20
5 2001 "C" "United Kingdom"  65159 1 1 4   1.958802   1.958802  2451683 1.5323496   4.7 6.12017e+16 21
5 2002 "C" "United Kingdom"  53417 0 1 4      2.076      2.076  2505100 1.5204024  5.04 6.22673e+16 21
5 2003 "C" "United Kingdom"  83217 0 1 4      2.076      2.076  2588317 1.3765004  4.81 6.70562e+16 21
5 2004 "C" "United Kingdom"  59176 0 1 4      2.076      2.076  2647493 1.3903975  4.59 7.82735e+16 21
5 2010 "C" "United Kingdom"      . 1 1 3   3.564247   3.697813  2796536  2.492655  7.79 6.99631e+16 24
5 2011 "C" "United Kingdom"  35676 0 . .   5.454876   5.670001  2832212 3.8561125  8.04 7.67414e+16 24
5 2012 "C" "United Kingdom"  40510 0 . .   5.454876       5.67  2872722  2.573235  7.88 7.59697e+16 24
5 2013 "C" "United Kingdom"  62803 0 . .   5.454876   5.670001  2935525 2.2916667  7.52 7.64401e+16 24
5 2014 "C" "United Kingdom"  84036 0 . .   5.454876   5.670001  3019561   1.45112  6.11 7.94942e+16 24
6 1990 "C" "United States"       . 1 1 2       14.9       14.9 10650444  5.397956   5.6           . 24
6 1991 "C" "United States"  -11531 0 1 2       14.9       14.9 10638913  4.234964   6.8           . 25
6 1992 "C" "United States"  374749 1 1 2       14.9       14.9 11013662 3.0288196   7.5           . 25
6 1993 "C" "United States"  303072 0 1 2   .0375137   .0375137 11316734  2.951657   6.9           . 26
6 1994 "C" "United States"  455928 1 1 2      -.781      -.781 11772662  2.607442  6.12           . 26
6 2000 "C" "United States"       . 1 1 2      1.463      1.463 14931055  3.376857  3.99           . 29
6 2001 "C" "United States"  142493 0 1 2   5.542341   5.542341 15073548  2.826171  4.73           . 30
6 2002 "C" "United States"  255639 1 1 2      5.767      5.767 15329187 1.5860317  5.78           . 30
6 2003 "C" "United States"  428635 0 1 2      5.767      5.767 15757822 2.2700949  5.99           . 31
6 2004 "C" "United States"  607079 1 1 2      5.767      5.767 16364901  2.677237  5.53           . 31
6 2010 "C" "United States"       . 1 1 2       -.36       -.36 17784695 1.6400435  9.63           . 34
6 2011 "C" "United States"  275644 0 1 2       -.36       -.36 18060339 3.1568415  8.95           . 35
6 2012 "C" "United States"  411899 1 1 2       -.36       -.36 18472238 2.0693374  8.07           . 35
6 2013 "C" "United States"  340236 0 1 2   1.298654   1.298654 18812474 1.4648327  7.37           . 36
6 2014 "C" "United States"  430387 0 1 .       1.39       1.39 19242861  1.622223  6.17           . 36
end

[George Ford]

Not fully grasping the problem, collapse allows weights and you can collapse by period and group?

[Giorgio Di Stefano]

Not fully grasping the problem, collapse allows weights and you can collapse by period and group?

That is correct!
To state again the problem, I created and estimated some data for an entire period and Then for sub periods. There are three types of groups, and sub-periods are in decades. I would like to obtain the weighed average for the data created for the sub-periods and groups, first as the entire period and then by groups., If possible without losing my original data. Generating new variables is fine.

How can I do that?

[George Ford]

What are you weighting by?

[Giorgio Di Stefano]

What are you weighting by?

By time and country size, that is gdp

[George Ford]

Does this not do it?

collapse (mean) x1 x2 [aw=gdp] , by(period group)

[Giorgio Di Stefano]

Does this not do it?

collapse (mean) x1 x2 [aw=gdp] , by(period group)

Yes, but I would prefer to generate new variables instead of missing the existing, as the collapse does. I need to re-use the original for graphs etc compared to the collapsed.
I found asgen. How would that work here?
https://www.statalist.org/forums/for...d-average-mean

[George Ford]

1. You could create 2 datasets (and use frames, if that's your thing).
2. You could create group/year averages using asgen, then mark the first in each group/year and "if first"
3. If the dataset isn't too large, you could preserve, collapse, do stuff, restore.

[Giorgio Di Stefano]

1. You could create 2 datasets (and use frames, if that's your thing).
2. You could create group/year averages using asgen, then mark the first in each group/year and "if first"
3. If the dataset isn't too large, you could preserve, collapse, do stuff, restore.

Thank you so much, George ! Much appreciated!