CODEWITHSUNDEEP
pythonpandasdataframe
Arvinth Kumar
Arvinth Kumar

Reputation: 1014

Calculate weighted average based on 2 columns using a pandas/dataframe

I have the following dataframe df. I want to calculate a weighted average grouped by each date and Sector level

 date     Equity    value    Sector   Weight
2000-01-31  TLRA    20      RG Index     0.20
2000-02-28  TLRA    30      RG Index     0.20
2000-03-31  TLRA    40      RG Index     0.20
2000-01-31   RA     50      RG Index     0.30
2000-02-28   RA     60      RG Index     0.30
2000-03-31   RA     70      RG Index     0.30
2000-01-31  AAPL    80      SA Index     0.50
2000-02-28  AAPL    90      SA Index     0.50
2000-03-31  AAPL    100     SA Index     0.50
2000-01-31  SPL     110     SA Index     0.60
2000-02-28  SPL     120     SA Index     0.60
2000-03-31  SPL     130     SA Index     0.60

There can be many Equity under a Sector . I want Sector level weighted Average based on Weight column.

Expected Output:

date        RG Index       SA Index
2000-01-31  19               106  
2000-02-28  24               117
2000-03-31  29               138

I tried below code, but i am not getting expected output. Please help

g = df.groupby('Sector')
df['wa'] = df.value / g.value.transform("sum") * df.Weight
df.pivot(index='Sector', values='wa')

Upvotes: 3

Views: 885

Answers (1)

BENY
BENY

Reputation: 323226

More like pivot problem first assign a new columns as product of value and weight 

df.assign(V=df.value*df.Weight).pivot_table(index='date',columns='Sector',values='V',aggfunc='sum')
Out[328]: 
Sector      RGIndex  SAIndex
date                        
2000-01-31     19.0    106.0
2000-02-28     24.0    117.0
2000-03-31     29.0    128.0

Upvotes: 4

Related Questions