Weighted mean for each year in panel data

[Ananya Kotia]

Hello,

I have a panel dataset of 100 firms each with 5 years of data. For each of the five years, I want to calculate the mean of the debt ratio variable of all firms weighted by the assets variable of each firm. Thus, I want a weighted average measure for debt ratio by year. A snapshot of the data is as follows:

id	year	assets	debt ratio
19420	2001	364.3988	0.019435
19420	2002	365.549	0.080575
19420	2003	365.7212	-0.02357
19420	2004	364.701	0.010654
19420	2005	372.4104	0.060513
59796	2001	294.1355	0.043929
59796	2002	293.2148	0.063242
59796	2003	293.305	0.149902
59796	2004	377.0879	0.154791
59796	2005	463.3691	0.205261
192855	2001	1754.285	0.244634
192855	2002	1745.32	0.185563
192855	2003	2055.629	0.227559
192855	2004	2457.257	0.27855
192855	2005	2748.261	0.273795
243328	2001	9156.501	0.102508
243328	2002	8912.646	0.086499
243328	2003	8790.42	0.065551
243328	2004	8562.688	0.069552
243328	2005	8542.187	0.069065

Can you please advise me on the code I can use for this? Thanks.

[Clyde Schechter]

I think you want:

egen weighted_total_assets = total(assets), by(year)
egen weighted_total_debt = total(assets*debt_ratio), by(year)
gen weighted_mean_debt_ratio = weighted_total_debt/weighted_total_assets

[Ananya Kotia]

Thank you, that worked just fine!

[Ina Ode]

Hi, I have a similar issue, my data is an unbalanced panel, please what do you suggest is the best method to create a weighted average and what code would be appropriate. I am dealing with 30 companies over 10 years. Below is the data set I am working with:

COMPANY	SECTOR	YEAR	ECON	ENVIRON	SOCIAL	CSR
7UP	CONSUMER GOODS	2005	1	0	4	5
7UP	CONSUMER GOODS	2006	1	0	4	5
7UP	CONSUMER GOODS	2007	1	0	4	5
7UP	CONSUMER GOODS	2008	1	0	4	5
ACCESS	FINANCIAL SERVICES	2006	1	0	4	5
ACCESS	FINANCIAL SERVICES	2007	1	0	4	5
ACCESS	FINANCIAL SERVICES	2008	2	5	11	18
ACCESS	FINANCIAL SERVICES	2009	2	8	10	20
ACCESS	FINANCIAL SERVICES	2010	2	9	10	21
ACCESS	FINANCIAL SERVICES	2011	2	10	11	23
ACCESS	FINANCIAL SERVICES	2012	2	10	14	26
ACCESS	FINANCIAL SERVICES	2013	2	10	15	27
ACCESS	FINANCIAL SERVICES	2014	3	10	16	29
CADBURY	CONSUMER GOODS	2005	3	0	7	10
CADBURY	CONSUMER GOODS	2006	2	0	7	9
CADBURY	CONSUMER GOODS	2007	2	0	7	9
CADBURY	CONSUMER GOODS	2011	2	0	7	9
CADBURY	CONSUMER GOODS	2012	2	2	7	11
DANGCEM	INDUSTRIAL GOODS	2010	1	1	6	8
DANGCEM	INDUSTRIAL GOODS	2011	2	3	6	11
DANGCEM	INDUSTRIAL GOODS	2012	2	4	6	12
DANGCEM	INDUSTRIAL GOODS	2013	2	5	8	15
DANGCEM	INDUSTRIAL GOODS	2014	3	5	5	13
DANGSUGAR	CONSUMER GOODS	2007	1	0	2	3
DANGSUGAR	CONSUMER GOODS	2009	2	0	2	4
DANGSUGAR	CONSUMER GOODS	2010	2	0	2	4
DANGSUGAR	CONSUMER GOODS	2011	2	0	2	4
DANGSUGAR	CONSUMER GOODS	2012	2	0	2	4
DANGSUGAR	CONSUMER GOODS	2013	2	0	3	5
DANGSUGAR	CONSUMER GOODS	2014	2	0	4	6
DIAMONDBANK	FINANCIAL SERVICES	2006	1	0	6	7
DIAMONDBANK	FINANCIAL SERVICES	2007	1	0	6	7
DIAMONDBANK	FINANCIAL SERVICES	2008	1	0	6	7
DIAMONDBANK	FINANCIAL SERVICES	2009	1	0	6	7
DIAMONDBANK	FINANCIAL SERVICES	2010	1	0	6	7
DIAMONDBANK	FINANCIAL SERVICES	2011	1	0	7	8
DIAMONDBANK	FINANCIAL SERVICES	2012	1	0	8	9
DIAMONDBANK	FINANCIAL SERVICES	2013	1	1	9	11
DIAMONDBANK	FINANCIAL SERVICES	2014	1	1	9	11
ETI	FINANCIAL SERVICES	2006	1	0	9	10
ETI	FINANCIAL SERVICES	2007	1	2	9	12
ETI	FINANCIAL SERVICES	2008	1	3	9	13
ETI	FINANCIAL SERVICES	2009	1	3	9	13
ETI	FINANCIAL SERVICES	2010	1	3	10	14
ETI	FINANCIAL SERVICES	2011	1	5	10	16
ETI	FINANCIAL SERVICES	2012	1	7	10	18
ETI	FINANCIAL SERVICES	2013	1	7	10	18
ETI	FINANCIAL SERVICES	2014	1	7	11	19
FBNH	FINANCIAL SERVICES	2007	1	0	7	8
FBNH	FINANCIAL SERVICES	2008	1	0	7	8
FBNH	FINANCIAL SERVICES	2009	1	0	10	11
FBNH	FINANCIAL SERVICES	2010	2	0	10	12
FBNH	FINANCIAL SERVICES	2011	2	0	12	14
FBNH	FINANCIAL SERVICES	2012	2	2	12	16
FBNH	FINANCIAL SERVICES	2013	2	2	15	19
FBNH	FINANCIAL SERVICES	2014	2	2	15	19
FCMB	FINANCIAL SERVICES	2005	2	0	5	7
FCMB	FINANCIAL SERVICES	2006	2	0	5	7
FCMB	FINANCIAL SERVICES	2007	2	0	5	7
FCMB	FINANCIAL SERVICES	2008	2	0	5	7
FCMB	FINANCIAL SERVICES	2009	2	0	7	9
FCMB	FINANCIAL SERVICES	2010	2	3	8	13
FCMB	FINANCIAL SERVICES	2011	2	3	9	14
FCMB	FINANCIAL SERVICES	2012	2	5	10	17
FCMB	FINANCIAL SERVICES	2013	2	5	12	19
FCMB	FINANCIAL SERVICES	2014	2	7	12	21
FLOURMILL	CONSUMER GOODS	2005	2	0	4	6
FLOURMILL	CONSUMER GOODS	2006	2	0	4	6
FLOURMILL	CONSUMER GOODS	2008	1	0	4	5
FLOURMILL	CONSUMER GOODS	2009	2	0	4	6
FLOURMILL	CONSUMER GOODS	2012	2	0	4	6
FLOURMILL	CONSUMER GOODS	2013	2	2	4	8
FLOURMILL	CONSUMER GOODS	2014	2	2	4	8
FORTEOIL	OIL AND GAS	2007	1	0	4	5
FORTEOIL	OIL AND GAS	2009	0	0	0	0
FORTEOIL	OIL AND GAS	2012	1	0	3	4
FORTEOIL	OIL AND GAS	2013	1	0	3	4
FORTEOIL	OIL AND GAS	2014	1	0	5	6
GUARANTY	FINANCIAL SERVICES	2006	1	0	5	6
GUARANTY	FINANCIAL SERVICES	2007	1	0	6	7
GUARANTY	FINANCIAL SERVICES	2009	1	0	6	7
GUARANTY	FINANCIAL SERVICES	2011	1	2	6	9
GUARANTY	FINANCIAL SERVICES	2012	2	6	13	21
GUARANTY	FINANCIAL SERVICES	2013	2	7	13	22
GUARANTY	FINANCIAL SERVICES	2014	2	7	13	22
GUINNESS	CONSUMER GOODS	2005	2	1	6	9
GUINNESS	CONSUMER GOODS	2006	2	1	6	9
GUINNESS	CONSUMER GOODS	2007	2	1	6	9
GUINNESS	CONSUMER GOODS	2008	2	1	6	9
GUINNESS	CONSUMER GOODS	2009	2	1	6	9
GUINNESS	CONSUMER GOODS	2010	2	1	6	9
GUINNESS	CONSUMER GOODS	2011	2	1	6	9
GUINNESS	CONSUMER GOODS	2012	3	7	8	18
GUINNESS	CONSUMER GOODS	2013	3	6	8	17
GUINNESS	CONSUMER GOODS	2014	3	6	8	17
INTBREW	CONSUMER GOODS	2007	1	0	1	2
INTBREW	CONSUMER GOODS	2014	1	0	6	7
JBERGER	CONSTRUCTION/REAL ESTATE	2009	2	1	4	7
JBERGER	CONSTRUCTION/REAL ESTATE	2010	2	1	5	8
JBERGER	CONSTRUCTION/REAL ESTATE	2011	2	1	5	8
JBERGER	CONSTRUCTION/REAL ESTATE	2012	2	1	5	8
JBERGER	CONSTRUCTION/REAL ESTATE	2013	2	2	5	9
JBERGER	CONSTRUCTION/REAL ESTATE	2014	2	2	6	10
MOBIL	OIL AND GAS	2006	1	0	2	3
MOBIL	OIL AND GAS	2007	1	0	3	4
MOBIL	OIL AND GAS	2013	1	1	3	5
MOBIL	OIL AND GAS	2014	1	1	3	5
NESTLE	CONSUMER GOODS	2005	2	3	10	15
NESTLE	CONSUMER GOODS	2006	2	5	10	17
NESTLE	CONSUMER GOODS	2007	3	5	11	19
NESTLE	CONSUMER GOODS	2008	3	5	11	19
NESTLE	CONSUMER GOODS	2009	3	8	11	22
NESTLE	CONSUMER GOODS	2010	3	8	12	23
NESTLE	CONSUMER GOODS	2011	3	6	14	23
NESTLE	CONSUMER GOODS	2012	3	6	14	23
NESTLE	CONSUMER GOODS	2013	3	6	14	23
NESTLE	CONSUMER GOODS	2014	3	8	14	25
NB	CONSUMER GOODS	2005	2	0	5	7
NB	CONSUMER GOODS	2006	2	1	5	8
NB	CONSUMER GOODS	2007	2	1	5	8
NB	CONSUMER GOODS	2008	2	1	5	8
NB	CONSUMER GOODS	2009	3	1	5	9
NB	CONSUMER GOODS	2010	3	1	6	10
NB	CONSUMER GOODS	2011	3	3	6	12
NB	CONSUMER GOODS	2012	3	3	6	12
NB	CONSUMER GOODS	2013	3	5	6	14
NB	CONSUMER GOODS	2014	3	6	6	15
OANDO	OIL AND GAS	2005	1	1	8	10
OANDO	OIL AND GAS	2006	1	1	8	10
OANDO	OIL AND GAS	2007	1	1	9	11
OANDO	OIL AND GAS	2008	1	2	9	12
OANDO	OIL AND GAS	2009	1	3	10	14
OANDO	OIL AND GAS	2010	2	3	10	15
OANDO	OIL AND GAS	2011	2	3	12	17
OANDO	OIL AND GAS	2012	2	3	13	18
OANDO	OIL AND GAS	2013	2	4	14	20
OANDO	OIL AND GAS	2014	2	4	13	19
PZ	CONSUMER GOODS	2005	1	0	4	5
PZ	CONSUMER GOODS	2006	1	0	7	8
PZ	CONSUMER GOODS	2007	1	0	7	8
PZ	CONSUMER GOODS	2008	1	0	4	5
PZ	CONSUMER GOODS	2009	1	0	5	6
PZ	CONSUMER GOODS	2010	2	8	8	18
PZ	CONSUMER GOODS	2011	2	8	8	18
PZ	CONSUMER GOODS	2012	2	9	8	19
PZ	CONSUMER GOODS	2013	2	9	8	19
PZ	CONSUMER GOODS	2014	2	10	8	20
STANBIC	FINANCIAL SERVICES	2006	1	0	8	9
STANBIC	FINANCIAL SERVICES	2007	1	0	8	9
STANBIC	FINANCIAL SERVICES	2008	1	0	8	9
STANBIC	FINANCIAL SERVICES	2009	1	0	11	12
STANBIC	FINANCIAL SERVICES	2010	1	0	11	12
STANBIC	FINANCIAL SERVICES	2011	1	0	11	12
STANBIC	FINANCIAL SERVICES	2012	1	0	11	12
STANBIC	FINANCIAL SERVICES	2013	1	0	11	12
STANBIC	FINANCIAL SERVICES	2014	1	0	11	12
STERLINGBANK	FINANCIAL SERVICES	2006	1	0	8	9
STERLINGBANK	FINANCIAL SERVICES	2007	1	0	8	9
STERLINGBANK	FINANCIAL SERVICES	2008	1	0	8	9
STERLINGBANK	FINANCIAL SERVICES	2009	1	0	10	11
STERLINGBANK	FINANCIAL SERVICES	2010	1	2	10	13
STERLINGBANK	FINANCIAL SERVICES	2011	1	2	11	14
STERLINGBANK	FINANCIAL SERVICES	2012	1	2	12	15
STERLINGBANK	FINANCIAL SERVICES	2013	2	2	12	16
STERLINGBANK	FINANCIAL SERVICES	2014	3	3	12	18
TOTAL	OIL AND GAS	2006	2	0	4	6
TOTAL	OIL AND GAS	2007	2	0	5	7
TOTAL	OIL AND GAS	2009	2	0	5	7
TRANSCORP	CONGLOMERATES	2006	0	0	0	0
TRANSCORP	CONGLOMERATES	2007	0	0	0	0
TRANSCORP	CONGLOMERATES	2008	0	0	0	0
TRANSCORP	CONGLOMERATES	2009	0	0	0	0
TRANSCORP	CONGLOMERATES	2010	1	0	3	4
TRANSCORP	CONGLOMERATES	2011	1	0	3	4
TRANSCORP	CONGLOMERATES	2012	1	0	3	4
TRANSCORP	CONGLOMERATES	2013	1	0	3	4
TRANSCORP	CONGLOMERATES	2014	1	0	5	6
UACN	CONGLOMERATES	2005	1	0	4	5
UACN	CONGLOMERATES	2006	1	0	4	5
UACN	CONGLOMERATES	2007	1	0	6	7
UACN	CONGLOMERATES	2008	1	0	6	7
UACN	CONGLOMERATES	2009	1	0	7	8
UACN	CONGLOMERATES	2010	1	0	8	9
UACN	CONGLOMERATES	2011	1	1	8	10
UACN	CONGLOMERATES	2012	2	1	9	12
UACN	CONGLOMERATES	2013	2	1	9	12
UACN	CONGLOMERATES	2014	2	1	9	12
UNILEVER	CONSUMER GOODS	2006	1	0	5	6
UNILEVER	CONSUMER GOODS	2007	0	0	0	0
UNILEVER	CONSUMER GOODS	2011	1	0	5	6
UNILEVER	CONSUMER GOODS	2014	1	3	6	10
UBN	FINANCIAL SERVICES	2005	1	0	8	9
UBN	FINANCIAL SERVICES	2009	1	0	8	9
UBN	FINANCIAL SERVICES	2010	1	0	8	9
UBN	FINANCIAL SERVICES	2011	1	2	8	11
UBN	FINANCIAL SERVICES	2012	1	2	8	11
UBN	FINANCIAL SERVICES	2013	1	2	8	11
UBN	FINANCIAL SERVICES	2014	1	2	8	11
UBA	FINANCIAL SERVICES	2005	1	0	7	8
UBA	FINANCIAL SERVICES	2006	1	3	7	11
UBA	FINANCIAL SERVICES	2007	1	3	7	11
UBA	FINANCIAL SERVICES	2008	1	3	8	12
UBA	FINANCIAL SERVICES	2009	1	3	10	14
UBA	FINANCIAL SERVICES	2010	2	3	10	15
UBA	FINANCIAL SERVICES	2011	2	3	11	16
UBA	FINANCIAL SERVICES	2012	2	3	11	16
UBA	FINANCIAL SERVICES	2013	2	3	11	16
UBA	FINANCIAL SERVICES	2014	2	3	12	17
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2009	2	2	2	6
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2010	2	3	2	7
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2011	3	5	2	10
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2012	3	6	2	11
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2013	3	6	3	12
WAPCO - Lafarge	CONSTRUCTION/REAL ESTATE	2014	3	7	3	13
ZENITHBANK	FINANCIAL SERVICES	2005	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2006	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2007	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2008	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2009	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2010	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2011	1	0	4	5
ZENITHBANK	FINANCIAL SERVICES	2012	1	0	6	7
ZENITHBANK	FINANCIAL SERVICES	2013	2	6	12	20
ZENITHBANK	FINANCIAL SERVICES	2014	2	6	12	20

[Nick Cox]

Weighted average of what with what weights? Please also read and follow http://www.statalist.org/forums/help#stata on how to present data.