How to create a weighted average for multiple observations for a variable by borrower_id and year

[Saleh sharmah]

Borroer_id Year Loan amount Loan maturity Loan interest
1 2011 101 60 8.5
1 2011 95 55 5.7
2 2011 85 55 8.6
3 2011 90 44 6.5
3 2012 82 46 7.5
4 2011 65 60 6.2
4 2012 67 90 5.4
5 2011 85 45 6.5
5 2011 62 78 4.5
6 2012 55 23 6.5

I would like to calculate the weighted average for the (loan amount, loan maturity, and loan interest ) according to the weight by loan amount relative to the total amount in each borrower_id.
For instance, for borrower 1, the weighted average is (101/196, 95/196), respectively equal to 0.51, 0.48 and using these percentages to calculate the weighted loan average = (101*0.51+95*0.48)=97.11
Using the same weighted average percentage created from borrower_1 loans (0.51, 0.48), I would like to use them to be applied to both the loan maturity and loan interest as well.

thank you so much in advance for your help.

[Joro Kolev]

Calculate the total amount of loans by -egen, total()- by id and then calculate the weighted average by my egen function wmean()

. findit _gwmean

and follow instructions to install.

If you cannot figure it out, post a usable dataset using -dataex-, because I am not able to import your data.

[George Ford]

Interesting. wmean tends to round a lot.

could use -asgen-

ssc install asgen
asgen loanamount_w = loanamount, weight(loanamount), by(borroer_id)

[Joro Kolev]

Interesting. wmean tends to round a lot.

could use -asgen-

ssc install asgen
asgen loanamount_w = loanamount, weight(loanamount), by(borroer_id)

This does not sound right. Can you show an example of what you mean?

I remember it was an issue while I was writing it whether to keep the precision of the variable the user chooses, or to do it in double, and I just looked at the code: Not only that it does not round a lot, but it over-writes your choice, that is, if you want to generate float, it overwrites and computes in double.

[George Ford]

egen loanamount_w = wmean(loanamount), by(borroer_id)
asgen loanamount_w2 = loanamount, weight(loanamount) by(borroer_id)

* Example generated by -dataex-. For more info,    type help dataex
clear
input byte borroer_id int(year loanamount) byte    loanmat double(loanint    loanamount_w    loanamount_w2)
1 2011 101 60 8.5   98 98.09183673469387
1 2011  95 55 5.7   98 98.09183673469387
2 2011  85 55 8.6   85                85
3 2011  90 44 6.5   86 86.18604651162791
3 2012  82 46 7.5   86 86.18604651162791
4 2011  65 60 6.2   66 66.01515151515152
4 2012  67 90 5.4   66 66.01515151515152
5 2011  85 45 6.5 73.5 75.29931972789116
5 2011  62 78 4.5 73.5 75.29931972789116
6 2012  55 23 6.5   55                55
end

[George Ford]

Doh! I needed a weight on egen.

[George Ford]

Thanks for the wmean command. I'll use it (correctly) in the future as I prefer the syntax.

[Saleh sharmah]

Thanks Joro and George for your efforts to help. Joro, would you kindly use the example provided by George to help answer my questions correctly.

[Saleh sharmah]

Thanks Joro and George for your efforts to help. Joro, would you kindly use the example provided by George to help answer my questions correctly.

[Saleh sharmah]

Also Joro, would you kindly share with the link to download the )gwmean command as the link below is not working .
http://fmwww.bc.edu/RePEc/bocode/_/_gwmean.sthlp:
Thanks

[Joro Kolev]

To install write from within Stata

. findit _gwmean

a window will open on which there will be the option to install the function.

If I understand correctly you just want the weighted average of loanmat loanint, where this is weighted by loanamount; and this calculated by borrower. What you describe seems to be the definition of weighted average:

. egen wavgloanmat = wmean(loanmat), by( borroer_id) weight(loanamount)

. egen wavgloanint = wmean( loanint ), by( borroer_id) weight(loanamount)

. list, sepby( borroer_id)

     +------------------------------------------------------------------------+
     | borroe~d   year   loanam~t   loanmat   loanint   wavglo~at   wavglo~nt |
     |------------------------------------------------------------------------|
  1. |        1   2011        101        60       8.5   57.576531   7.1428571 |
  2. |        1   2011         95        55       5.7   57.576531   7.1428571 |
     |------------------------------------------------------------------------|
  3. |        2   2011         85        55       8.6          55         8.6 |
     |------------------------------------------------------------------------|
  4. |        3   2011         90        44       6.5   44.953488   6.9767442 |
  5. |        3   2012         82        46       7.5   44.953488   6.9767442 |
     |------------------------------------------------------------------------|
  6. |        4   2011         65        60       6.2   75.227273   5.7939394 |
  7. |        4   2012         67        90       5.4   75.227273   5.7939394 |
     |------------------------------------------------------------------------|
  8. |        5   2011         85        45       6.5   58.918367   5.6564626 |
  9. |        5   2011         62        78       4.5   58.918367   5.6564626 |
     |------------------------------------------------------------------------|
 10. |        6   2012         55        23       6.5          23         6.5 |
     +------------------------------------------------------------------------+

[Joro Kolev]

Doh! I needed a weight on egen.

Without weight specified, it computes the standard average, that is the same as -egen, mean()-.

[Joro Kolev]

Thanks for the wmean command. I'll use it (correctly) in the future as I prefer the syntax.

I also prefer that many functions are put under the same syntax of -egen-, I am a big fan of -egen- and I use it a lot. And I would prefer the most if Stata Corp works more actively on developing the -egen- set of functions, for example they themselves can make all the functions there take weights. Currently only users develop the -egen- set and it is a bit of a zoo out there.

[Saleh sharmah]

Thanks Joro for your helpful response. A follow up question: how different is asgen command from gwmean in calculating the weighted average for a variable?

thanks again for your help

[Joro Kolev]

Thanks Joro for your helpful response. A follow up question: how different is asgen command from gwmean in calculating the weighted average for a variable?

thanks again for your help

They must be the same. The definition of a weighted arithmetic mean is SUMi Wi/(SUMi Wi)*Xi, so whichever command purports to calculate a weighted mean, must be using the same formula.

So the choice between the user written -egen, wmean()- and -asgen- is a matter of taste. They must be doing the same.