loop to gen a mean by groups recursivelly disregarding one group

[Federica Di Marcantonio]

Dears,

I have a total of 175 purchasers across five regions. I need to create a variable that for each farmers have the mean of a variable called penalties of the farmers selling to other purchasers (so excluding the value of the observation i am looking at).
Schematically this would be

Farmer	Regions	Purchasers	Penalities	my_new_var
1	A	Leex	1	4.3
2	A	Leex	2	4.3
3	A	Leex	3	4.3
4	A	Coop_s	5	3
5	A	Coop_s	2	3
6	A	Nilson	6	2.6
7	B	Roos	2	3.5
8	B	Roos	8	3.5
9	B	Xia	2	4.75
10	B	Xia	3	4.75
11	B	Fax	8	3.75
12	B	Fax	1	3.75

so my new var for farmers selling to the same purchaser (Leex) would be:
(5+2+6)/3
to the second Coop_s
(1+2+3+6)/4...and so on

I tried with this loop but I am not producing what i would need

foreach x of numlist 1/175  {
   bys Reg: egen mean_Penalities`x'= mean(Penalities) if  names!=`x'
    

}

any suggestion?

Thank you in advance

Federica

[Mike Lacy]

I'm confused by several things in your description and so find it hard to offer you help:

1) Your "bysort" prefix refers to Regions, but your description says nothing about the relevance of Region.
2) You say that you want a variable for each farmer, but I'm getting the impression that you actually want a distinct value of your new variable for every Farmer-Purchaser pair. So, perhaps you mean something like: "For every distinct pair defined by the values of Farmer and Purchaser, I'd like a new variable that ..... (I'm ignoring the Region issue.)
3) Your code mentions a variable "names," but you don't tell us about the relevance of that variable.

I'd suggest you try showing your question to a colleague and re-posting it. Some other people here might be able to decipher your question by abstracting the rules from your example, but I think a clarified description will help us help you.

[Raffaele Grotti]

Hi Federica,
this should work for you.
Note that the loop works over purchasers rather than farmers.

levelsof purchasers, loc(lp)
gen newvar = .
tempvar nv nv2
foreach l of local lp {
    bys regions: egen `nv' =  mean(penalities) if purchasers!="`l'"
    bys regions: egen `nv2' = max(`nv')
    replace newvar = `nv2' if purchasers=="`l'"
    drop `nv' `nv2'
}

Best,
Raffaele

[Robert Picard]

No loop needed, just add up penalties and number of observations in the relevant by-groups and then calculate the desired mean from there. Something like:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte farmer str1 regions str6 purchasers byte penalities float my_new_var
 1 "A" "Leex"   1  4.3
 2 "A" "Leex"   2  4.3
 3 "A" "Leex"   3  4.3
 4 "A" "Coop_s" 5    3
 5 "A" "Coop_s" 2    3
 6 "A" "Nilson" 6  2.6
 7 "B" "Roos"   2  3.5
 8 "B" "Roos"   8  3.5
 9 "B" "Xia"    2 4.75
10 "B" "Xia"    3 4.75
11 "B" "Fax"    8 3.75
12 "B" "Fax"    1 3.75
end

bysort regions purchasers: egen rptotal = total(penalities)
by regions purchasers: gen rpN = _N
by regions: egen rtotal = total(penalities)
by regions: gen wanted = (rtotal - rptotal) / (_N-rpN)

list, sepby(regions) noobs

and the results

. list, sepby(regions) noobs

  +---------------------------------------------------------------------------------------+
  | farmer   regions   purcha~s   penali~s   my_new~r   rptotal   rpN   rtotal     wanted |
  |---------------------------------------------------------------------------------------|
  |      4         A     Coop_s          5          3         7     2       19          3 |
  |      5         A     Coop_s          2          3         7     2       19          3 |
  |      2         A       Leex          2        4.3         6     3       19   4.333333 |
  |      3         A       Leex          3        4.3         6     3       19   4.333333 |
  |      1         A       Leex          1        4.3         6     3       19   4.333333 |
  |      6         A     Nilson          6        2.6         6     1       19        2.6 |
  |---------------------------------------------------------------------------------------|
  |     11         B        Fax          8       3.75         9     2       24       3.75 |
  |     12         B        Fax          1       3.75         9     2       24       3.75 |
  |      8         B       Roos          8        3.5        10     2       24        3.5 |
  |      7         B       Roos          2        3.5        10     2       24        3.5 |
  |      9         B        Xia          2       4.75         5     2       24       4.75 |
  |     10         B        Xia          3       4.75         5     2       24       4.75 |
  +---------------------------------------------------------------------------------------+

[Federica Di Marcantonio]

Dears,

Many thanks for your help!!! I was able to create the var I wanted. ...MIke sorry for not being clear enough in my post

Best Regards,

Federica