Transform data set to sub-periods with corresponding averages of all other variables

[Andreas Nord]

Dear Statalist,

After been using the advices here for several years without being a member, it is now time for my first question regarding help. Hopefully I make my self clear and follow the faq.

I am trying to create "sub periods" from my data set. My data is between 1980-2014 on countries, years, elections etc. Every country has elections differently and thus for elections my voting data is constant for 3-5 years while all other data is still changing which becomes a problem when doing regressions.

I want to be able to do something like "If election take average of <varlist> to next election.". I need to have averages of all variables (per variable) in my data set corresponding to the amount of years between two elections in one country.

I am looking forward to answers and to clarify if its needed.
Thank you!

* Example generated by -dataex-. To install: ssc install dataex
clear
input long Country int(Year Elections) double(ip tradeopen popvote)
1 1980     . .2192838302 66.82500604862834    0
1 1981     . .2166291172   68.396550301208    0
1 1982     . .2090544166 64.11711392349588    0
1 1983  8514 .2126598031 62.47218498724764    0
1 1984     . .2223274877  66.5750060351522    0
1 1985     . .2248569158 70.08316147624951    0
1 1986  9823  .216564363 64.01459721216203  9.7
1 1987     . .2194225564 62.85372272692686  9.7
1 1988     . .2283248892   66.562550344062  9.7
1 1989     . .2380358262 70.53835879455089  9.7
1 1990 11237 .2440088484 71.48836948192262 16.6
1 1991     . .2482316847 70.04840589285222 16.6
1 1992     . .2485028767 67.63016746537562 16.6
1 1993     . .2361753364 63.26505049155278 16.6
1 1994 12700 .2510384177 65.98709333788209 22.5
1 1995 13134 .2562872031    68.25660110272 21.9
1 1996     . .2599885442 70.08367112068267 21.9
1 1997     . .2687096818 74.86769021558281 21.9
1 1998     . .2727328475  76.9271493915785 21.9
1 1999 14520 .2738576729 78.26014034397156 26.9
1 2000     . .2851453874  85.3604956179232 26.9
1 2001     . .2941603639 87.53666476393525 26.9
1 2002 15668 .2921390095 86.94816799966057   10
1 2003     . .2963529696 86.38738241628248   10
1 2004     . .3081622988 90.79234569292967   10
1 2005     . .3137299618 94.03380568330545   10
1 2006 17075 .3188569952 98.08831120534364 17.9
1 2007     .  .322258439  100.733254061926 17.9
1 2008 17803 .3211315156 102.0736827317061 28.2
1 2009     .  .301992492 87.06223097427956 28.2
1 2010     . .3230191689 99.01979623695625 28.2
1 2011     . .3292108445 105.1027886297255 28.2
1 2012     . .3303752091 105.1521774278381 28.2
1 2013 19630  .332350068 104.0664140863733 24.1
1 2014     . .3372894244 103.5035352510687 24.1
1 2015     . .3422488354 102.4300546702535 24.1
1 2016     . .3450714632 101.0020250810702 24.1
2 1980     . .2816145769  102.306683920574    0
2 1981  7982 .2809780569 109.4176861613412  2.7
2 1982     .  .283057865 117.0639042762904  2.7
2 1983     . .2800385733 119.5094603178238  2.7
2 1984     . .2843406502 127.2864360507372  2.7
2 1985  9417 .2828523194 122.9633391064458    0
2 1986     . .2875734248 111.4945548135144    0
2 1987 10208 .2955695668  109.105628431747    0
2 1988     .  .302948319 115.5598990323904    0
2 1989     . .3134892514 124.0119131587489    0
2 1990     . .3155399501 120.5696727146501    0
2 1991 11650 .3183815591 117.7127732340387  9.8
2 1992     . .3236491167 114.2157113395169  9.8
2 1993     . .3249747551  108.201663534969  9.8
2 1994     .  .331990655 112.3889369502991  9.8
2 1995 12924 .3359774354  115.511365408443 10.1
2 1996     . .3436807272 118.0600938027919 10.1
2 1997     . .3531981125 124.3666932632705 10.1
2 1998     . .3596413258 123.4844956876552 10.1
2 1999 14408 .3590086961 123.9972402610697  9.9
2 2000     . .3795161999 141.0790714966192  9.9
2 2001     .  .375420947  138.694465221206  9.9
2 2002     . .3735640572 135.1248849817734  9.9
2 2003 15843 .3751812716 131.9903126453944 13.7
2 2004     . .3807863321 136.0378332487476 13.7
2 2005     . .3903834839 143.3762209420292 13.7
2 2006     . .3950167822 147.6939014351527 13.7
2 2007 17327 .4000258135 151.1636218587954   14
2 2008     . .4062239782 158.9080450842626   14
2 2009     . .3900065166 136.3567578633131   14
2 2010 18426 .4051918683 151.1001765267372  7.8
2 2011     .  .417446467 162.7536656605285  7.8
2 2012     . .4171383949 163.9949770335543  7.8
2 2013     . .4174767817 162.2186431251685  7.8
2 2014 19868 .4286128623 164.6983927979592  3.7
2 2015     . .4324144044 160.1720103296707  3.7
2 2016     . .4487138877 164.1156472597038  3.7
6 1980     .           .                 .    .
6 1981     .           .                 .    .
6 1982     .           .                 .    .
6 1983     .           .                 .    .
6 1984     .           .                 .    .
6 1985     .           .                 .    .
6 1986     .           .                 .    .
6 1987     .           .                 .    .
6 1988     .           .                 .    .
6 1989     .           .                 .    .
6 1990 11117           . 63.80639159580962    0
6 1991     .           . 71.61825999276348    0
6 1992 11845           . 78.89511604541855    6
6 1993     . .1907769366 79.13457296431604    6
6 1994     . .1987149572 75.69958560215952    6
6 1995     . .2173680212 83.89495520493278    6
6 1996 13301 .2273601739 81.49325044463582    8
6 1997     .  .237533033 85.07416814509439    8
6 1998 14050 .2469036941 84.68860956704492  3.9
6 1999     . .2525193269 86.26125292485742  3.9
6 2000     . .2696520409 98.23312080013685  3.9
6 2001     . .2837223159 99.30997399456218  3.9
6 2002 15506 .2894371739  91.5259818230906    0
6 2003     . .2986614059 95.01519722229932    0
6 2004     . .3366525698 113.8683352250686    0
6 2005     . .3494116662 122.0152585154173    0
end
format %tdnn/dd/CCYY Elections
label values Country nyCountry
label def nyCountry 1 "Austria", modify
label def nyCountry 2 "Belgium", modify
label def nyCountry 6 "Czech Republic", modify

[William Lisowski]

Welcome to Statalist, and thank you for the very helpful data example.

I hope the following example, run on your data, demonstrates enough technique to start you on your way.

bysort Country (Year): replace Elections = Elections[_n-1] if missing(Elections)
sort Country Elections
foreach v of varlist ip tradeopen {
    by Country Elections: egen double avg_`v' = mean(`v')
    }
sort Country Year
list Country Year Elections ip avg_ip tradeopen avg_tradeopen if Country==1, ///
     noobs sepby(Country Elections)

  +-----------------------------------------------------------------------------+
  | Country   Year    Elections          ip      avg_ip   tradeopen   avg_tra~n |
  |-----------------------------------------------------------------------------|
  | Austria   1980            .   .21928383   .21498912   66.825006   66.446223 |
  | Austria   1981            .   .21662912   .21498912    68.39655   66.446223 |
  | Austria   1982            .   .20905442   .21498912   64.117114   66.446223 |
  |-----------------------------------------------------------------------------|
  | Austria   1983    4/24/1983    .2126598   .21994807   62.472185   66.376784 |
  | Austria   1984    4/24/1983   .22232749   .21994807   66.575006   66.376784 |
  | Austria   1985    4/24/1983   .22485692   .21994807   70.083161   66.376784 |
  |-----------------------------------------------------------------------------|
  | Austria   1986   11/23/1986   .21656436   .22558691   64.014597   65.992307 |
  | Austria   1987   11/23/1986   .21942256   .22558691   62.853723   65.992307 |
  | Austria   1988   11/23/1986   .22832489   .22558691    66.56255   65.992307 |
  | Austria   1989   11/23/1986   .23803583   .22558691   70.538359   65.992307 |
  |-----------------------------------------------------------------------------|
  | Austria   1990    10/7/1990   .24400885   .24422969   71.488369   68.107998 |
  | Austria   1991    10/7/1990   .24823168   .24422969   70.048406   68.107998 |
  | Austria   1992    10/7/1990   .24850288   .24422969   67.630167   68.107998 |
  | Austria   1993    10/7/1990   .23617534   .24422969    63.26505   68.107998 |
  |-----------------------------------------------------------------------------|
  | Austria   1994    10/9/1994   .25103842   .25103842   65.987093   65.987093 |
  |-----------------------------------------------------------------------------|
  | Austria   1995   12/17/1995    .2562872   .26442957   68.256601   72.533778 |
  | Austria   1996   12/17/1995   .25998854   .26442957   70.083671   72.533778 |
  | Austria   1997   12/17/1995   .26870968   .26442957    74.86769   72.533778 |
  | Austria   1998   12/17/1995   .27273285   .26442957   76.927149   72.533778 |
  |-----------------------------------------------------------------------------|
  | Austria   1999    10/3/1999   .27385767   .28438781    78.26014     83.7191 |
  | Austria   2000    10/3/1999   .28514539   .28438781   85.360496     83.7191 |
  | Austria   2001    10/3/1999   .29416036   .28438781   87.536665     83.7191 |
  |-----------------------------------------------------------------------------|
  | Austria   2002   11/24/2002   .29213901   .30259606   86.948168   89.540425 |
  | Austria   2003   11/24/2002   .29635297   .30259606   86.387382   89.540425 |
  | Austria   2004   11/24/2002    .3081623   .30259606   90.792346   89.540425 |
  | Austria   2005   11/24/2002   .31372996   .30259606   94.033806   89.540425 |
  |-----------------------------------------------------------------------------|
  | Austria   2006    10/1/2006     .318857   .32055772   98.088311   99.410783 |
  | Austria   2007    10/1/2006   .32225844   .32055772   100.73325   99.410783 |
  |-----------------------------------------------------------------------------|
  | Austria   2008    9/28/2008   .32113152   .32114585   102.07368   99.682135 |
  | Austria   2009    9/28/2008   .30199249   .32114585   87.062231   99.682135 |
  | Austria   2010    9/28/2008   .32301917   .32114585   99.019796   99.682135 |
  | Austria   2011    9/28/2008   .32921084   .32114585   105.10279   99.682135 |
  | Austria   2012    9/28/2008   .33037521   .32114585   105.15218   99.682135 |
  |-----------------------------------------------------------------------------|
  | Austria   2013    9/29/2013   .33235007   .33923995   104.06641   102.75051 |
  | Austria   2014    9/29/2013   .33728942   .33923995   103.50354   102.75051 |
  | Austria   2015    9/29/2013   .34224884   .33923995   102.43005   102.75051 |
  | Austria   2016    9/29/2013   .34507146   .33923995   101.00203   102.75051 |
  +-----------------------------------------------------------------------------+

[Andreas Nord]

Thank you for your help! I managed with the help of the command and some manual work to take care of it!

Have a great weekend!