0

I want to calculate the average of all members of the group I am in, but not include myself in the average. Suppose that the group variable is called group and I want to take the average of val1 by Group, excluding myself. The new column I wish to create is avg. The data looks as follows (with the correct values of avg inputed so you can see what I mean). 

Obs   Group   val1    avg
1     A       6       8
2     A       8       6
3     B       10      13
4     C       4       4
5     C       2       5
6     C       6       3
7     B       12      12
8     B       14      11

If I wanted to include myself in the calculation it would be straightforward. I would just do:

bysort Group: egen avg = mean(val1)

But how do I implement this with the wrinkle that I don't include myself?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
bill999
  • 2,147
  • 8
  • 51
  • 103
  • 1
    Please try to look at existing resources. This has been a FAQ since 2001 (http://www.stata.com/support/faqs/data-management/creating-variables-recording-properties/) and as such an answer is findable through `search`. – Nick Cox Nov 09 '14 at 09:39
  • Question and answer have no connection to **sorting**, so tag removed. – Nick Cox Nov 09 '14 at 09:41
  • 1
    See also http://stackoverflow.com/questions/9577808/using-if-qualifier-with-egen-in-stata Same point about looking at existing resources.... – Nick Cox Nov 09 '14 at 09:59

1 Answers1

4

One way is looping through all observations:

clear
set more off

*----- example data -----

input ///
Obs   str1 Group   val1    avg
1     A       6       8
2     A       8       6
3     B       10      13
4     C       4       4
5     C       2       5
6     C       6       3
7     B       12      12
8     B       14      11
end

list, sepby(Group)

*----- what you want -----

encode Group, gen(group)

gen avg2 = .

forvalues j = 1/`=_N' {

    summarize val1 if group == group[`j'] & _n != `j', meanonly              
    replace avg2 = r(mean) in `j'
}

list, sepby(group)

Another way is using egen functions:

<snip>    

*----- what you want -----

encode Group, gen(group)

bysort group: egen totval = total(val1)
by group: egen cval = count(val1)
generate avg2 = (totval - val1) / (cval - 1)

list, sepby(group)

There is a nice article available on the web that covers this topic:

The Stata Journal (2014) 14, Number 2, pp. 432–444, Speaking Stata: Self and others, by Nicholas J. Cox.

Roberto Ferrer
  • 11,024
  • 1
  • 21
  • 23
  • 1
    The key is that average of every other value is (total of all - this value) / (count of all - 1), but missings complicate this, so watch out. – Nick Cox Nov 09 '14 at 10:55