Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Weighted mean in panel data with weights as different variables

    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

    Code:
    * 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    .

  • #2
    I'm not 100% sure I've understood what you want, but I think this will at least start you in a useful direction.
    Code:
    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
    Code:
    . 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 |
      +-----------------------------------+

    Comment

    Working...
    X