Weighted mean in panel data with weights as different variables

[Mihir Sharma]

Hello,

I am working with an annual panel data from 5 European countries (2000-2005). For each country and year, I have a variable x and the distance of that country's capital from each of the other country's capital cities (kmdist). For each country-year observation, I want to calculate the mean of x of all other countries, weighted by the kmdist of each country. E.g., for Austria in 2000, I want to compute the average of x for Belgium, Czech, and Denmark in 2000, weighted by their respective kmdist variables.

I am quite lost on how to start. Any advice would be greatly appreciated!

Below is an extract of the data.

Many thanks,
Mihir

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 country int year float x int(kmdistAustria kmdistBelgium kmdistCzech kmdistDenmark)
"Austria"        2000   -.1725926    .  903  259  827
"Austria"        2001  -.11703704    .  903  259  827
"Austria"        2002 -.018518522    .  903  259  827
"Austria"        2003   .04962962    .  903  259  827
"Austria"        2004   .10518519    .  903  259  827
"Austria"        2005   .12666667    .  903  259  827
"Belgium"        2000   -.1688889  903    .  725  781
"Belgium"        2001  -.10888889  903    .  725  781
"Belgium"        2002 -.010370375  903    .  725  781
"Belgium"        2003   .05777777  903    .  725  781
"Belgium"        2004   .11333334  903    .  725  781
"Belgium"        2005   .13111112  903    .  725  781
"Czech Republic" 2000   -.1514815  259  725    .  577
"Czech Republic" 2001  -.09148148  259  725    .  577
"Czech Republic" 2002   .02296296  259  725    .  577
"Czech Republic" 2003    .0911111  259  725    .  577
"Czech Republic" 2004   .12777779  259  725    .  577
"Czech Republic" 2005   .12518519  259  725    .  577
"Denmark"        2000   -.1851852  827  781  577    .
"Denmark"        2001   -.1525926  827  781  577    .
"Denmark"        2002  -.05407408  827  781  577    .
"Denmark"        2003   .01407407  827  781  577    .
"Denmark"        2004   .06962963  827  781  577    .
"Denmark"        2005    .0874074  827  781  577    .

[William Lisowski]

I'm not 100% sure I've understood what you want, but I think this will at least start you in a useful direction.

rename country from
reshape long kmdist, i(year from) j(to) string
replace to = "Czech Republic" if to=="Czech"
drop if from==to
list if year==2000, sepby(from) noobs
collapse (mean) x [fw=kmdist], by(to year)
list if year==2000, noobs

. rename country from

. reshape long kmdist, i(year from) j(to) string
(note: j = Austria Belgium Czech Denmark)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       24   ->      96
Number of variables                   7   ->       5
j variable (4 values)                     ->   to
xij variables:
kmdistAustria kmdistBelgium ... kmdistDenmark->kmdist
-----------------------------------------------------------------------------

. replace to = "Czech Republic" if to=="Czech"
variable to was str7 now str14
(24 real changes made)

. drop if from==to
(24 observations deleted)

. list if year==2000, sepby(from) noobs

  +-------------------------------------------------------------+
  | year             from               to           x   kmdist |
  |-------------------------------------------------------------|
  | 2000          Austria          Belgium   -.1725926      903 |
  | 2000          Austria   Czech Republic   -.1725926      259 |
  | 2000          Austria          Denmark   -.1725926      827 |
  |-------------------------------------------------------------|
  | 2000          Belgium          Austria   -.1688889      903 |
  | 2000          Belgium   Czech Republic   -.1688889      725 |
  | 2000          Belgium          Denmark   -.1688889      781 |
  |-------------------------------------------------------------|
  | 2000   Czech Republic          Austria   -.1514815      259 |
  | 2000   Czech Republic          Belgium   -.1514815      725 |
  | 2000   Czech Republic          Denmark   -.1514815      577 |
  |-------------------------------------------------------------|
  | 2000          Denmark          Austria   -.1851852      827 |
  | 2000          Denmark          Belgium   -.1851852      781 |
  | 2000          Denmark   Czech Republic   -.1851852      577 |
  +-------------------------------------------------------------+

. collapse (mean) x [fw=kmdist], by(to year)

. list if year==2000, noobs

  +-----------------------------------+
  | year               to           x |
  |-----------------------------------|
  | 2000          Austria    -.173398 |
  | 2000          Belgium   -.1703216 |
  | 2000   Czech Republic   -.1755271 |
  | 2000          Denmark   -.1656939 |
  +-----------------------------------+

[Mihir Sharma]

This works, thanks so much!